Search Columns with Similar Data, Concatenate to One Cell

[Rzrbkpk]

I have a worksheet that has multiple columns with similar data. The data is related by a pair of columns, so I need to search one column for a parameter then return the information on the same row in the next column. Here's a sample of the data:

TYPE	NAME	         TYPE	  NAME
Subject	Robert Smith	 Witness  Sam Smith
Subject	John Doe	 Subject  Jane Doe
Witness	Joe Johnson	 Witness  Jim Johnson
Caller	Sue Smith	 Subject  Johnny Storm

I'm looking for those with the "Subject" type. So the results from the first row would have "Robert Smith" in the cell I designate. The second row would have both "John Doe" and "Jane Doe" in the cell I designate...and so on. Any help or starting points would be greatly appreciate

 

[SkipVought]

Just make ONE TABLE with 2 columns. It is how Excel is designed to work.

Then your answer is a snap with an AutoFilter

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rzrbkpk]

Skip,
I understand your reasoning, but doing it that way would be somewhat cumbersome. There's general 20 (10 pairs) columns and multiple reports/worksheets. It's the way the program exports the data to CSV that's problematic. Sorry, I didn't explain enough.

 

[SkipVought]

but doing it that way would be somewhat cumbersome.

And you think that manipulating the data as you have describerd is NOT?

So here's your choice, as I see it.

1. Maintain the extremely cumbersome workbook/whrosheet structure that makes most Excel feature for data maipulation totally useless and write klugy code to achieve your outcome. (BLEEEAAACH!!!)

2. Make a new sheet that combines the data in all the sheets in a coherent manner, from which all sorts of Excel features can be deployed. (workable)

Furthermore, it seems that the actual problem is much more cumbersome than first presented, when you stated, "There's general 20 (10 pairs) columns and multiple reports/worksheets." So it's not just TWO columns of data on one sheet, but maybe as many as TWENTY columns of data on MULTIPLE sheets; with different structures??? (mulitple reports???)

How 'bout being up front with the specs!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]