Copying selected information across multiple columns

[EH123]

Hello all!

I am attempting to copy selected information into the corresponding columns. Below is an example of the spreadsheet I am working on.

[TABLE="width: 2"]
<tbody>[TR]
[TD]Sponsor 1
[/TD]
[TD]Sponsor 2
[/TD]
[TD]Sponsor 3
[/TD]
[TD]Sponsor 4
[/TD]
[TD]Sponsor 5
[/TD]
[TD]Investor
[/TD]
[TD]Thanks
[/TD]
[TD][/TD]
[TD]Sponsor
[/TD]
[TD]Investor
[/TD]
[TD]Thanks
[/TD]
[/TR]
[TR]
[TD]Example A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Example A
[/TD]
[TD]Example A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Example A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Example A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Example A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Example A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Example B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Example B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Example B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Example B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am trying to copy this information into the corresponding columns.
Below is the desired result

[TABLE="width: 2"]
<tbody>[TR]
[TD]Sponsor 1
[/TD]
[TD]Sponsor 2
[/TD]
[TD]Sponsor 3
[/TD]
[TD]Sponsor 4
[/TD]
[TD]Sponsor 5
[/TD]
[TD]Investor
[/TD]
[TD]Thanks
[/TD]
[TD][/TD]
[TD]Sponsor
[/TD]
[TD]Investor
[/TD]
[TD]Thanks
[/TD]
[/TR]
[TR]
[TD]Example A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Example A
[/TD]
[TD]Example A
[/TD]
[TD][/TD]
[TD]Example A
[/TD]
[TD]Example A
[/TD]
[TD]Example A
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Example A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Example A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Example A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Example A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Example A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Example A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Example A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Example A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Example B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Example B
[/TD]
[TD][/TD]
[TD]Example B
[/TD]
[TD][/TD]
[TD]Example B
[/TD]
[/TR]
[TR]
[TD]Example B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Example B
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Example B
[/TD]
[TD]Example B
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I was wondering if there was a formula that could do this for me. The spreadsheet is over 1000 rows so doing this manually could take weeks.

Any help at all would be greatly appreciated!

Thanks so much

Ed

 

[steve the fish]

You can provided there are no duplicated headers. Are there? Something like this:

=IF(LEN(INDEX(Sheet2!$A$1:$K$10,ROW(),MATCH(A$1,Sheet2!$A$1:$K$1,0)))>0,INDEX(Sheet2!$A$1:$K$10,ROW(),MATCH(A$1,Sheet2!$A$1:$K$1,0)),"")

 

[EH123]

Hi Steve the Fish

Thanks for the quick response! There are no exact duplicated headers.

Would you mind explaining how this formula works so I can modify it if needs be?

Thanks again

Ed

 

[steve the fish]

This part is the main formula, the rest is just to prevent a sea of zeroes instead of blanks:

INDEX(Sheet2!$A$1:$K$10,ROW(),MATCH(A$1,Sheet2!$A$1:$K$1,0)

This is our range to extract information from: Sheet2!$A$1:$K$10

INDEX takes two arguments in this formula, the row required in the range and the column required in the range. The row part is derived from ROW() which means our formula will return the row in which it is housed. Say our formula is in A2, ROW() will return 2. The MATCH will search for the value in A1 within the range Sheet2!$A$1:$K$1. When found it will return a number. This number is the relative position of the match within the range. Lets say A1 contains 'Hello' and in Sheet2 cell D1 we find 'Hello', MATCH would return 4 because it has found its match in the 4th cell of the range. So in this example the formula would return whatever is in D2 of Sheet2.