Forum Discussion
Benbee250
Sep 16, 2022Copper Contributor
ORGANIZE FILE
Hello, I have an excel file with 2k names/phone numbers and addresses. I need to organize the file to make sure the phone numbers are in one column all landline phones and in the other all mobile. ...
mtarler
Sep 16, 2022Silver Contributor
you have multiple phone numbers on a line so if there are 2 land lines and 2 mobile numbers you want just the 1st of each or are you suggesting adding columns so you have something like
Phone0_MobileNumber, Phone0_LandNumber,... Phone1_MobileNumber,Phone1_LandNumber,...
If the latter then simply add 2 columns for each phone number section (and name accordingly) and add formulas like: =IF(S2="Mobile",R2,"") and =IF(S2="Land Line",R2,"") is the corresponding columns and adjust the cell accordingly.
better yet would be if the whole set is defined as a table (Format as a Table) and then use the table reference [@[Phone0_Number]] instead of the R2 cell reference
Phone0_MobileNumber, Phone0_LandNumber,... Phone1_MobileNumber,Phone1_LandNumber,...
If the latter then simply add 2 columns for each phone number section (and name accordingly) and add formulas like: =IF(S2="Mobile",R2,"") and =IF(S2="Land Line",R2,"") is the corresponding columns and adjust the cell accordingly.
better yet would be if the whole set is defined as a table (Format as a Table) and then use the table reference [@[Phone0_Number]] instead of the R2 cell reference
Benbee250
Sep 16, 2022Copper Contributor
what i mean is i want it to be organized like this sample attached. https://ibb.co/NT6vDzG.
want all the landlines in one column and mobiles.
- mtarlerSep 17, 2022Silver Contributoras dexter noted, it would be easier if we had a sample sheet (personal/confidential info stripped).
but as I noted above a simple IF statement could work. In each of your NEW "Landline" columns you have IF(S2="Land Line",R2,"") where S2 and R2 which are for "Phone0" to the columns for "Phone1" ... and the same thing for new "Mobile" columns except ="Mobile" - DexterG_IIISep 17, 2022Iron Contributor
Benbee250 The link to your original file is broken, could you please re-post?
Okay I see the link was pasted twice and I can now see the picture but it would be helpful to have some data to work with instead. I think it's an easy solution but need to test.
- Benbee250Sep 17, 2022Copper Contributor
So, This is the original. this link is what my file looks like https://ibb.co/YfkrBcN.
I would like to have the landlines and mobile in order but in the same row if that makes sense.
So like Landline 1| Landline 2... Mobile 1| Mobile 2.... l to look like this https://ibb.co/NT6vDzG .
If needed send me your email i can send you the file to test.
- mtarlerSep 18, 2022Silver Contributorif you click on my name you can send PM and should be able to attach a file that way