Rows to Column

[grcshekar]

Hi,

Please refer attached sample data

I want to convert data in ORIGINAL to CONVERTED .

I have more than 10,000 addresses

[Czeslaw]

Formula
Mr.

=IFERROR(INDEX(ORIGINAL!A$1:A$1000;AGGREGATE(15;6;ROW($A$1:$A$10000)/(LEFT(ORIGINAL!$A$1:$A$10000;2)="Mr");ROWS(A$1:A1)));"")

and

=IFERROR(INDEX(ORIGINAL!$A$1:$A$10000;AGGREGATE(15;6;ROW($1:$10000)/(MATCH($A2;ORIGINAL!$A$1:$A$10000;0)-MATCH($A1;ORIGINAL!$A$1:$A$10000;0)-1>=COLUMNS($B:B));ROWS(B$1:B1)+COLUMNS($B:B))+MATCH($A1;ORIGINAL!$A$1:$A$10000;0)-ROWS(A$1:A1));"")

[grcshekar]

This works only if title is Mr, Mrs.
How to modify for "Dr", "Col", "Lt.Col" Etc

[Czeslaw]

New formulas
1.

=IFERROR(INDEX(ORIGINAL!A$1:A$10000;AGGREGATE(15;6;ROW($A$1:$A$9999)/ISNUMBER(SEARCH({"Mr."\"Mrs."\"Ms."\"Dr."\"Col."};ORIGINAL!$A$1:$A$10000));ROWS(A$1:A1)));"")

2.

=IFERROR(IF(MATCH($A2;ORIGINAL!$A$1:$A$10000;0)-MATCH($A1;ORIGINAL!$A$1:$A$10000;0)-1>=COLUMNS($B:B);INDEX(ORIGINAL!$A$1:$A$10000;COLUMNS($B:B)+MATCH($A1;ORIGINAL!$A$1:$A$10000;0));"");"")

[alansidman]

If you are willing to go down the Power Query Road, then the attached solves the problem. You do need to list your prefix titles in a separate table.