Matching contents from one cell into another and displaying the contents back

[hahmed87]

Hi All,



I have a little bit of a tricky situation. I have two worksheets. Sheet1 contains a file name with claim numbers like below:



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 490"]
<tbody>[TR="class: grid"]
[TD="width: 490"]File Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR="class: grid"]
[TD="width: 88"]Date Created[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 490"]
<tbody>[TR="class: grid"]
[TD="width: 490"]AAAA File name with additional characters.doc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR="class: grid"]
[TD="width: 88, align: right"]20170809[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 490"]
<tbody>[TR="class: grid"]
[TD="width: 490"]BBBB File name with additional characters.doc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR="class: grid"]
[TD="width: 88, align: right"]20170809[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 490"]
<tbody>[TR="class: grid"]
[TD="width: 490"]CCCC File name with additional characters.pdf[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR="class: grid"]
[TD="width: 88, align: right"]20170809[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 490"]
<tbody>[TR="class: grid"]
[TD="width: 490"]DDDD File name with additional characters.doc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR="class: grid"]
[TD="width: 88, align: right"]20170809[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 490"]
<tbody>[TR="class: grid"]
[TD="width: 490"]AAAA & EEEE File name with additional characters.pdf[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR="class: grid"]
[TD="width: 88, align: right"]20170809[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 490"]
<tbody>[TR="class: grid"]
[TD="width: 490"]More Characters here BBBB & CCCC File name with additional characters.doc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR="class: grid"]
[TD="width: 88, align: right"]20170809[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2 contains claim numbers as below:



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]Claim[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]AAAA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]BBBB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]CCCC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]DDDD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Data[/TD]
[/TR]
</tbody>[/TABLE]


I would like to know if there are any formulas out there in which I can look for the Claim number is Sheet2 in Sheet1 and it gives me back all the file names that contain that claim number. For example, Claim AAAA from Sheet2 is in 2 file names in Sheet1, so I would like it to return to me[TABLE="width: 490"]
<tbody>[TR]
[TD="width: 490"]"AAAA File name with additional characters.doc" and [TABLE="width: 490"]
<tbody>[TR]
[TD="width: 490"]"AAAA & EEEE File name with additional characters.pdf"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Please let me know if any additional information is needed.



Thanks!

 

[hahmed87]

Try to add a row in front of the header row of Sheet1...

When I add rows above the header rows, the formula changes the file names.

 

[Aladin Akyurek]

When I add rows above the header rows, the formula changes the file names.

Wrong results, that is.

See the workbook that implements te suggestion I made in post #3 : Dropbox - hahmed87 sublist.xlsx