How to search and concatenate across multiple sheets

[jonjonjonjon]

Hi guys,

I'm looking to search down specific columns which are separated equally within a sheet. Once the data is found in that column, i would like to return the column title (and concatenate the titles if there is data in multiple columns).

The search needs to be done across multiple sheets. The structure (location of each column) in each sheet is the same.

Eg:

Problem:

[TABLE="width: 491"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Car[/TD]
[TD](3 columns in between)[/TD]
[TD]Bike[/TD]
[TD](3 columns in between)[/TD]
[TD]Boat[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD][/TD]
[TD]Red[/TD]
[TD][/TD]
[TD]Orange [/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD][/TD]
[TD]Purple [/TD]
[TD][/TD]
[TD]White[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD][/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD]Black[/TD]
[/TR]
</tbody>[/TABLE]


Solution: (I have a list of Col A so just need to return Col B)
Col A Col B
[TABLE="width: 214"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Red[/TD]
[TD]Car, Bike[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]Car[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]Car, Bike,[/TD]
[/TR]
[TR]
[TD]Purple[/TD]
[TD]Bike[/TD]
[/TR]
[TR]
[TD]Orange [/TD]
[TD]Boat[/TD]
[/TR]
[TR]
[TD]White[/TD]
[TD]Boat[/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]Boat [/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

jonjonjonjon,

We can not tell where your raw data is located, sheet name(s), cells, cell formatting, cell formulae, rows, columns, and, we can not tell where the results should be, sheet name, cells, cell formatting, cell formulae, rows, columns.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data worksheets, and, what the results (manually formatted by you), should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729