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

H

hahmed87

New Member
Joined
Aug 9, 2017
Messages
6
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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
hahmed87 said:
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!
Click to expand...

in Sheet2,

B2=IFERROR(INDEX(Sheet1!$A$2:$A$7,SMALL(IF(ISNUMBER(SEARCH($A2,Sheet1!$A$2:$A$7)),ROW(Sheet1!$A$2:$A$7),""),COLUMN()-COLUMN($A$1))-1),"") press CTRL+SHIFT+ENTER

drag to the right then down
 
Upvote 0
In B2 of Sheet2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet1!$A$2:$A$7,SMALL(IF(ISNUMBER(SEARCH($A2,Sheet1!$A$2:$A$7)),ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2)+1),COLUMNS($B2:B2))),"")

which should not be affected by adding rows in front of the header row of Sheet1.
 
Upvote 0
jarjarbingie said:
in Sheet2,

B2=IFERROR(INDEX(Sheet1!$A$2:$A$7,SMALL(IF(ISNUMBER(SEARCH($A2,Sheet1!$A$2:$A$7)),ROW(Sheet1!$A$2:$A$7),""),COLUMN()-COLUMN($A$1))-1),"") press CTRL+SHIFT+ENTER

drag to the right then down
Click to expand...

Thank you for the above formula. Since I had more than the 7 claim file names I modified the formula a little bit:

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(ISNUMBER(SEARCH($A2,Sheet1!$A:$A)),ROW(Sheet1!$A:$A),""),COLUMN()-COLUMN($A1))-1),"")

it brought back a file name, but it wasn't a file name with anything like the claim number at all. For example if the claim number is "01016075", it brought back "C4B25571 LLR.pdf" what could I be doing wrong?

Thank you
 
Upvote 0
Aladin Akyurek said:
In B2 of Sheet2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet1!$A$2:$A$7,SMALL(IF(ISNUMBER(SEARCH($A2,Sheet1!$A$2:$A$7)),ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2)+1),COLUMNS($B2:B2))),"")

which should not be affected by adding rows in front of the header row of Sheet1.
Click to expand...

Thank you for the above formula. Since I had more than the 7 claim file names I modified the formula a little bit:

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(ISNUMBER(SEARCH($A3,Sheet1!$A:$A)),ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$2)+1),COLUMNS($B3:B3))),"")

it brought back a file name, but it wasn't a file name with anything like the claim number at all. For example if the claim number is "01016075", it brought back "C4B25571 LLR.pdf" what could I be doing wrong?

Thank you
 
Upvote 0
Aladin Akyurek said:
In B2 of Sheet2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet1!$A$2:$A$7,SMALL(IF(ISNUMBER(SEARCH($A2,Sheet1!$A$2:$A$7)),ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2)+1),COLUMNS($B2:B2))),"")

which should not be affected by adding rows in front of the header row of Sheet1.
Click to expand...

I really appreciate the help. Since I had more than the 7 claim file names I modified the formula a little bit:

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(ISNUMBER(SEARCH($A3,Sheet1!$A:$A)),ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$2)+1),COLUMNS($B3:B3))),"")

it brought back a file name, but it wasn't a file name with anything like the claim number at all. For example if the claim number is "01016075", it brought back "C4B25571 LLR.pdf" what could I be doing wrong?

Thank you
 
Upvote 0
hahmed87 said:
Thank you for the above formula. Since I had more than the 7 claim file names I modified the formula a little bit:

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(ISNUMBER(SEARCH($A2,Sheet1!$A:$A)),ROW(Sheet1!$A:$A),""),COLUMN()-COLUMN($A1))-1),"")

it brought back a file name, but it wasn't a file name with anything like the claim number at all. For example if the claim number is "01016075", it brought back "C4B25571 LLR.pdf" what could I be doing wrong?

Thank you
Click to expand...

try replacing -1 with +1, or try adjusting that number since the ranges got changed..
 
Upvote 0
hahmed87 said:
I really appreciate the help. Since I had more than the 7 claim file names I modified the formula a little bit:

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(ISNUMBER(SEARCH($A3,Sheet1!$A:$A)),ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$2)+1),COLUMNS($B3:B3))),"")

it brought back a file name, but it wasn't a file name with anything like the claim number at all. For example if the claim number is "01016075", it brought back "C4B25571 LLR.pdf" what could I be doing wrong?

Thank you
Click to expand...

Where (in which cell) did you implement the suggested formula?
 
Upvote 0
Thank you for the idea, I ended up having to put a +2 in the formula:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]{=IFERROR(INDEX(Sheet1!$A:$A, LARGE(IF(ISNUMBER(SEARCH($A2,Sheet1!$A:$A)),ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$2)+2),COLUMNS($B2:B2))),"")}

this worked for me.[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
hahmed87 said:
Thank you for the idea, I ended up having to put a +2 in the formula:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]{=IFERROR(INDEX(Sheet1!$A:$A, LARGE(IF(ISNUMBER(SEARCH($A2,Sheet1!$A:$A)),ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$2)+2),COLUMNS($B2:B2))),"")}

this worked for me.[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Try to add a row in front of the header row of Sheet1...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Macro to change one cell on a sheet, save sheet as PDF, then loop back and change that one cell on that sheet and repeat until the end
Replies
3
Views
663
IslandofBDA
I
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
390
ChrisMac1
ChrisMac1
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
849
Micron
M
D
  • Question Question
i need a excel macro to read from several pdf file kept under one folder and put in on a single excel output file
Replies
1
Views
442
ExcelToDAX
E
T
  • Solved
Create new workbooks based on cell content?
2
Replies
12
Views
775
kevin9999
K

Forum statistics

Threads
1,223,885
Messages
6,175,182
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top