0

I have a CSV file with 16 columns.

The first 10 (A-J) are contact information, the last 6 are different phone numbers, sometimes for the same contact.

My objective is to create duplicates of the first 10 columns with the new 11th column (K labeled PHONE) representing one phone number from the last 6 columns.

I am looking to keep all values from A to J as is in their own columns (no merging) Value in column K should have only one phone number.

Here is a link to a sample of data with “before”: what the original sheet was and “after” an example of what the result should look like.

2 Answers 2

0

A result similar to the expected can be obtained using the following formula:

={{"First Name","Last Name","Street Address","City","State","Zip",
    "Mail Street Address","Mail City","Mail State","Mail Zip","PHONE"};
  SORT(FILTER({
   {A3:J,K3:K};
   {A3:J,L3:L};
   {A3:J,M3:M};
   {A3:J,N3:N};
   {A3:J,O3:O};
   {A3:J,P3:P}},
 {K3:K;L3:L;M3:M;N3:N;O3:O;P3:P}<>""),1,FALSE)}

Work example here

4
  • Thank you! I got a REF error "Circular dependency detected. To resolve with iterative calculation, see File > Settings." How should I set the iterative calculation values?
    – Gus188
    Jul 21 at 18:58
  • Did you put the formula below the data? Try placing it to the right of the original table, for example in R2. Or change all references in the formula, specify the last line of data - it was A3:J, and it will become A3:J8. Yes, if you put the formula in R2, you might get the "not enough columns" error again. This is because the last column of your table is Z (26). 16 source data columns + 1 empty column + 11 result columns = 28. Just add the number of columns indicated in the error message
    – JohnSUN
    Jul 22 at 3:38
  • Please see this sheet
    – JohnSUN
    Jul 22 at 3:52
  • You are amazing! Thank you
    – Gus188
    Jul 22 at 7:38
0

This solution is not limited to just 8 lines. The formula is very complex and I would not be able to explain it to you. Try the test file

repeat

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.