excelvba

How to get common data from a large data set sheet into a small data set sheet with common entry in first column and match them accordingly

I have two sheets, Sheet 1 which has all the entries of Part Name in the system and specialized data of that part like sequence, due date and quality.

Sheet 2 consists of Part Name which is common in both the sheets and all the other columns differ from sheet 1 except part name, the sheet 2 columns are issue, error, quality, supervisor, line No.

Sheet 1:

Part Name Sequence Due Date quantity
A1 1 11-Mar 88
A2 2 12-Mar 90
A5 1 13-Mar 41
B5 1 14-Mar 69
B3 1 15-Mar 85
Z6 1 16-Mar 97
X7 2 17-Mar 77
H6 2 18-Mar 81
AB6 2 19-Mar 79
ACF 4 20-Mar 70
FF9 4 21-Mar 83
HJ 4 22-Mar 15
907 3 23-Mar 51
U9 3 24-Mar 10
7L 1 25-Mar 61
B1 2 26-Mar 95
K7 3 27-Mar 43
JY 1 28-Mar 66
O12 4 29-Mar 28

Sheet 2:

Part Number Issue error quality supervisor Line No.
Z6 1 0.029408618 OK AB 12
907 2 0.061354664 OK AB 23
B1 2 0.209159753 OK AD 23
O12 1.5 0.862573414 UI AD 24
ACF 2 0.403823252 OK GY 15
A1 3 0.452562474 OK NP 7
H69 1 0.327276608 OK TV 8

n both sheets, part names are spread in a random order.

I want to build a program so that the part name in sheet 2 gets the data from sheet 1 against their specific part number and I get a data set which consist of same amount of part numbers in sheet 2 but with the entries of sheet 1 column like sequence, due date, and quality.

Also there could be a case where parts from sheet one are not there in sheet 2 and vice versa

My final output should consist of sheet 2 columns entries like Part Number, Issue, error, quality, supervisor, Line No, Sequence, Due Date, Quality all in a single sheet for a specific number of parts mentioned in sheet 2.

Final output that I want:

Part Number Issue error quality supervisor Line No. Sequence Due Date quantity
Z6 1 0.029408618 OK AB 12 1 16-Mar 97
907 2 0.061354664 OK AB 23 3 23-Mar 51
B1 2 0.209159753 OK AD 23 2 26-Mar 95
O12 1.5 0.862573414 UI AD 24 4 29-Mar 28
ACF 2 0.403823252 OK GY 15 4 20-Mar 70
A1 3 0.452562474 OK NP 7 1 11-Mar 88
H69 1 0.327276608 OK TV 8

This is a sample data of the problem I am facing with, My actual data consist of thousands of entries in part number and hundreds of particulars for that part number

Solution

  • You just need XLOOKUP() in Sheet2 like-

    =XLOOKUP(A2,Sheet1!$A$2:$A$20,Sheet1!$B$2:$D$20,"")

    For spill array, you may try-

    =HSTACK(XLOOKUP(A2:A8,Sheet1!A2:A20,Sheet1!B2:B20,""),
XLOOKUP(A2:A8,Sheet1!A2:A20,Sheet1!C2:C20,""),
XLOOKUP(A2:A8,Sheet1!A2:A20,Sheet1!D2:D20,""))

    enter image description here