How duplicate rows in Google Sheets?

Question

I don't even know how to ask this question properly.

I have a list of properties and their owners, owners addresses, and phone numbers.

https://docs.google.com/spreadsheets/d/1-SUC9e53r-o73m5fLaV2FjkcGzLwW4mVPR0rUpw93aw/edit?usp=sharing

As you can see the last 10 columns are phone numbers. I want to create a new row for each number with all the same info in cells prior to the phone numbers. Anyone know how? Or can point me in the right direction what I need to learn. I'm trying to Youtube Marcos and learn how to do it myself but I don't even know what to search.

Answer 1

You will need to do a little cleanup of your data, first, because you want all those phone numbers without scientific notation. That probably looks like custom number format 0 (as many places as needed, no decimal places or commas), but you need to get back the digits lost after 901, say.

Once you get that done, suppose your input sheet is named "Input Sample" Then on your desired output sheet, copy your column headers for columns A through O into line 1. In O2, place the following formula, which will create your list of phone numbers in order of appearance: =arrayformula(transpose(split(textjoin(" ",false,'Input Sample'!O2:X)," "))) [What this formula does is put together a list of phone numbers, which have no spaces, separated by spaces, skipping any empty ones, takes it back apart, and orients it vertically.]

The rest of the work is to compose the list of fields that go with the phone number. There are other ways below. Since it seems like in your list no two people have the same phone number (once you clean up those exponentials), the following works. Back in cell A2 of your output sheet, place the formula =query('Input Sample'!A$2:X$59,"Select A, B, C, D, E, F, G, H, I, J, K, L, M, N"&" where O="&O2&" or P="&O2&" or Q="&O2&" or R="&O2&" or S="&O2&" or T="&O2&" or U="&O2&" or V="&O2&" or W="&O2&" or X="&O2,0) and drag that down as far as there are phone numbers. You will get back the column values corresponding to that phone number.

Note this approach will skip anyone without a phone number, which seems consistent with your goal.

EDIT: It can indeed be done without QUERY, which will alleviate possible issues with duplicates and lots of searching. Generate the phone number column as before. For simplicity, augment the input table with column Y, which has the number of phone numbers, by putting in Y2 =counta(O2:X2) and dragging that on down. In A2 of the desired output sheet, place the following: =transpose(split(arrayformula(textjoin("&",false,iferror(rept('Input Sample'!A2:A&"&",'Input Sample'!$Y2:$Y)))),"&")) and then drag it right through N2. How this one works is it builds up a string with repeated entries separated by "&" (which never occurs in the fields), where each entry occurs the number of times its original line has phone numbers, and then having put all those together, splits them back apart and orients vertically. The iferror handles people without any numbers. I am seeing some funkiness in the rightmost columns and toward the bottom when I use this approach. Adding more columns right to keep transpose from losing stuff or trimming off excess separators (which made the formula more unreadable) did not seem to help. [There are some limitations on REPT and strings that may be kicking in, though it is hard for me to say why specifically in the parts in the table where they do.]

MAJOR EDIT: Here is a simpler version that is more reliable because it does not need QUERY (which is fussy with duplicates) or REPT and intense string processing (which it seems bumps into limits or buggy code of mine).

STEP 0: Augment the input with column Y for the number of phones as above (with Y2 =counta(O2:X2) dragged from Y2 on down), and then add a column Z with the first index of phones. In Z2 place =if(Y2=0,1000000, 1+sum(Y$2:Y2)-Y2) and sort rows 2 on by Y2. Since the sort is ascending, that leaves everything alone except that it takes the phone-numberless entries to the bottom where they do no harm.

STEP 1: As above, into the result sheet copy over column headers for columns A through N, and give a header like "phone" to column O. Get these numbers as above with =arrayformula(transpose(split(textjoin(" ",false,'Input Sample'!O2:X)," "))) in O2.

STEP 2: In P1 place the header "source", for this column which will tell where in the original block of entries to find this row's values. In P2 place =IFERROR(MATCH(ROW()-1,'Input Sample'!Z$2:Z,0),P1) and drag it on down. This basically says if I am the first row for this set of phone numbers, great, use that, otherwise, I must be coming from the same row as the entry above me.

STEP 3: Now you can go grab all the data. In A2, place =index('Input Sample'!A$2:A,$P2) drag it across through column N and down as far as the numbers go (in your case row 198).