Excel Multiple Vlookups (?) with transposing (?)

[shannaganns]

Hi all - I'm new here and really hoping that someone can help me before I pull all my hair out!

This is a sample of the data I have. I have over 24k rows that look just like this.

[TABLE="width: 839"]
<tbody>[TR]
[TD]Group ID[/TD]
[TD]Merge Key[/TD]
[TD]Master Record[/TD]
[TD]Exclude Record[/TD]
[TD]Record ID[/TD]
[TD]Key Indicator[/TD]
[TD]Constituent Import ID[/TD]
[/TR]
[TR]
[TD]G-1[/TD]
[TD]|ALEXANDRE|||||[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]760105[/TD]
[TD]I[/TD]
[TD]00001-593-0000760105[/TD]
[/TR]
[TR]
[TD]G-1[/TD]
[TD]|ALEXANDRE|||||[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]760324[/TD]
[TD]I[/TD]
[TD]00001-593-0000760324[/TD]
[/TR]
[TR]
[TD]G-9[/TD]
[TD]|TATTOLI|||||[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]1148814[/TD]
[TD]I[/TD]
[TD]00001-593-0001148814[/TD]
[/TR]
[TR]
[TD]G-9[/TD]
[TD]|TATTOLI|||||[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]1148816[/TD]
[TD]I[/TD]
[TD]00001-593-0001148816[/TD]
[/TR]
[TR]
[TD]G-9[/TD]
[TD]|TATTOLI|||||[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]1149773[/TD]
[TD]I[/TD]
[TD]00001-593-0001149773[/TD]
[/TR]
[TR]
[TD]G-9[/TD]
[TD]|TATTOLI|||||[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]1148813[/TD]
[TD]I[/TD]
[TD]00001-593-0001148813[/TD]
[/TR]
[TR]
[TD]G-9[/TD]
[TD]|TATTOLI|||||[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]1149777[/TD]
[TD]I[/TD]
[TD]00001-593-0001149777[/TD]
[/TR]
[TR]
[TD]G-9[/TD]
[TD]|TATTOLI|||||[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]1149885[/TD]
[TD]I[/TD]
[TD]00001-593-0001149885[/TD]
[/TR]
</tbody>[/TABLE]

Each Group ID (column A) is a potential duplicate record in the database I run. I need to import this spreadsheet back into my database (as a csv) so that I can merge these records, but need to make sure that they get merged into the correct records.

To import the data back, the system requires the following: Column A to the Constituent Import ID # (last column) that has the word Yes in the Master Record column, located on the same line. Then for the following cells (same row) to have the Constituent Import ID # (last column) that has the word No in the Master Record column, located on the same line.

So making sure that the data only comes from the rows that include Group ID G-1, and then G-9. Then:

• Give me the number in the Constituent Import ID column in a NEW column
• BUT only if the answer in Master Record column = Yes

Then also:

• Give me the number in the Constituent Import ID column in a NEW column, next to the new column created for above
• BUT only if the answer in the Master Record column = No

Here’s what I need to end up seeing, just in case what I wrote above isn't clear. I color coded an example to be clear (see above and below).

[TABLE="class: grid, width: 945"]
<tbody>[TR]
[TD]Constituent Import ID if Master Record = Yes[/TD]
[TD]Constituent Import ID if Master Record = No[/TD]
[TD]Group ID[/TD]
[TD]Merge Key[/TD]
[TD]Master Record[/TD]
[TD]Exclude Record[/TD]
[TD]Record ID[/TD]
[TD]Key Indicator[/TD]
[TD]Constituent Import ID[/TD]
[/TR]
[TR]
[TD]00001-593-0000760324[/TD]
[TD]00001-593-0000760105[/TD]
[TD]G-1[/TD]
[TD]|ALEXANDRE|||||[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]760105[/TD]
[TD]I[/TD]
[TD]00001-593-0000760105[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]G-1[/TD]
[TD]|ALEXANDRE|||||[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]760324[/TD]
[TD]I[/TD]
[TD]00001-593-0000760324[/TD]
[/TR]
</tbody>[/TABLE]

Keeping in mind that Group ID G-9 has 6 rows of data - so the Constituent Import ID number that has the Master Record of Yes, goes in the first column, then the Constituent Import ID numbers that have the Master Record of No, go into the columns next to it.

I'm using Excel 2013, and have tried about a million different formulas, but I don't know formula's (except vlookup) very well, so I was really just trying to research online and play around on my own to see if I had any success, but clearly I didn't.

Any help you can provide would be SO SO SO appreciated!!

Thank you in advance!!!