How to transpose varied columns in excel?

Question

I've ton of rows like above.

Below table is ideal result.

I had worked with v-lookup, pivot table with no luck.

Is there any way to correctly match varied columns?

Answer 1

Yes it can be done. Here is a vba-less approach. I am using two working columns, A and B (but these could be tucked away elsewhere and hidden). My entries start somewhere below your data lines, in my case on line 6, where in A6 through D6 I put column headers:

sourceline    sourcecol    country    city

Now in A7 I put the number 1 to say start in the first row. In A8 I put the following formula which I dragged down as far as needed. =if(1+countif(indirect("A7:A"&(row()-1)),A7)=(counta(indirect("A"&A7&":Z"&A7))),A7+1,A7). What does it do? Well, it says let us see how many times the number right above me has occurred. If it is the same as the number of cities (assumed <=25) in the corresponding row, it is time to add 1 and move on to the next row. Otherwise, keep going with that same row.

In B7 I put the following formula and dragged it on down. =if(A7=A6,1+B6,2) It says, If I am still in the same row as the preceding entry, move over a column, else restart at column 2 (B).

Once I have the row and the column, for the country I needed the following in C7 and dragged down =indirect(address(A7,1)) which says find me the value in the row mentioned here and column 1 (A).

Similarly in D7 and dragged on down I have =indirect(address(A7,B7)) to get the city. That produces your desired table (maybe two columns over, but you could adjust that).

By leaving at least one blank line between the inputs and the headers of my outputs, you also end up with blanks at the bottom of the table if you drag your formula a bit beyond where it is needed.

You could probably simplify the formulas a smidge using the OFFSET function.