Match Data and Copy Details Info

[cyliyu]

I want to search the serial number stored in sheet 1 based on the serial number given by the user in sheet 2.
If the serial number was found, copy the info from sheet 1 into sheet 2. otherwise display not found or NA.
Need to take care of a duplicated serial number occur too. perhaps, auto inserts a line in between.
Any help, please?

 

[p45cal]

Two possibilities in the attached; on Sheet2 and Advanced Filter (with a tiny macro to set the criteria range and refresh), and a Pivot Table solution on Sheet3.

 

[cyliyu]

Thanks, P45cal for your help.
Sheet2 with macro is easier for the end user to use it.

I have some questions.
The macro will copy all the column info when the serial number was matched. is it possible to display only the selected column that is useful to the end user?

And the macro only allows executing once. Row B17 onward will need to delete before next execution. otherwise, run time error occurs.
I have added below statement after Sub Macro3()

Worksheets("Sheet2").Rows(17 & ":" & Worksheets("Sheet2").Rows.Count).Delete

 

[p45cal]

And the macro only allows executing once. Row B17 onward will need to delete before next execution. otherwise, run time error occurs.
I have added below statement after Sub Macro3()

Worksheets("Sheet2").Rows(17 & ":" & Worksheets("Sheet2").Rows.Count).Delete

NO!
Don't delete row B, that's what is required and determines which columns to show! For Advanced filter to work properly, the headers in row 17 and the header at cell B5 should EXACTLY match the headers of the table in Sheet 1. Even down to the dot (or not) after S/No
The CopyToRange in the line below should match the range of headers in row B, according the the headers you've chosen to include (the headers can be in any order by the way).
…ge:=critRng, CopyToRange:=.Range("B17:G17"), Unique:=…

The macro will copy all the column info when the serial number was matched. is it possible to display only the selected column that is useful to the end user?

That's because you have deleted row 17. See above.

ps. The button on Sheet2 should be assigned to Macro3, it seems to have been corrupted.

 

[bosco_yip]

Maybe, try this formula solution way.

1] In C6, copied down :

=IFERROR(INDEX(B$6:B$14,AGGREGATE(15,6,ROW(B$6:B$14)-ROW(B$5)/(COUNTIF(Sheet1!H$6:H$26,B$6:B$14)>=COLUMN($A:$H)),ROW(A1))),"")

2] In D6, copied across right to H6 and all copied down :

'=IF($C6="","",INDEX(Sheet1!$B$6:$H$26,AGGREGATE(15,6,ROW(Sheet1!$B$6:$B$26)-ROW(Sheet1!$B$5)/(Sheet1!$H$6:$H$26=$C6),COUNTIF($C$6:$C6,$C6)),MATCH(D$5,Sheet1!$B$5:$H$5,0)))

Regards
Bosco

 

[cyliyu]

The CopyToRange in the line below should match the range of headers in row B, according the the headers you've chosen to include (the headers can be in any order by the way).
…ge:=critRng, CopyToRange:=.Range("B17:G17"), Unique:=…

Thanks p45cal for the explaination. i managed to figure out the CopyToRange command.

That's because you have deleted row 17. See above.

ps. The button on Sheet2 should be assigned to Macro3, it seems to have been corrupted.

I have rewrite your code in my master worksheet and it work perfectly.

 

[cyliyu]

Maybe, try this formula solution way.

1] In C6, copied down :

=IFERROR(INDEX(B$6:B$14,AGGREGATE(15,6,ROW(B$6:B$14)-ROW(B$5)/(COUNTIF(Sheet1!H$6:H$26,B$6:B$14)>=COLUMN($A:$H)),ROW(A1))),"")

2] In D6, copied across right to H6 and all copied down :

'=IF($C6="","",INDEX(Sheet1!$B$6:$H$26,AGGREGATE(15,6,ROW(Sheet1!$B$6:$B$26)-ROW(Sheet1!$B$5)/(Sheet1!$H$6:$H$26=$C6),COUNTIF($C$6:$C6,$C6)),MATCH(D$5,Sheet1!$B$5:$H$5,0)))

Regards
Bosco

I initially tried the vlookup command, but it doesn't work.
Thanks Bosco for the Index/Aggregate commands.

If I copy a list of the serial number in the search column, and if happen same serial number results occurred 2 or more times, is it possible to auto shift the next search serial number to the next row?
For e.g. in the below search results, 00203 occurred 2 times, can 00237 auto shift to align with its search result in the same row?
This will help the user if the list has grown huge.

 

[bosco_yip]

I initially tried the vlookup command, but it doesn't work.
Thanks Bosco for the Index/Aggregate commands.
If I copy a list of the serial number in the search column, and if happen same serial number results occurred 2 or more times, is it possible to auto shift the next search serial number to the next row?
For e.g. in the below search results, 00203 occurred 2 times, can 00237 auto shift to align with its search result in the same row?
This will help the user if the list has grown huge.

1] The serial number in the search column is the Lookup Criteria and cannot in auto shift.

2] You may consider to use p45cal's post #.2 layout design, by putting the Input Table/Output Table in Top and Bottom layout.

Regards
Bosco

 

[cyliyu]

1] The serial number in the search column is the Lookup Criteria and cannot in auto shift.

2] You may consider to use p45cal's post #.2 layout design, by putting the Input Table/Output Table in Top and Bottom layout.

Regards
Bosco

Noted and thanks for your sharing. Appreciate it.