CODEWITHSUNDEEP
vbaexcel
Kinfe
Kinfe

Reputation: 43

How can i find duplicate and align a martching value in excel

I am trying to search a duplicate value in excel and sort the whole row by the matching records in B

I added Column between A and C, and added this code =VLOOKUP(A:A,B:B,1) but that find the duplicate it doesn't rearrange other records from column B forward

I want to find the duplicate and align the whole row

//here is the records looks like right now 

 Col A    Col B     Col C       Col D          Col E
 Item ID  Item ID   Item Desc   Purchase D     Sales Des
 600255   600111     xxxx        yyyy          zzzz
 600224   600255     mmmm        nnnn          yyyy
 600111   600224     aaaa        bbbb          cccc

Note: ColA data is copied from another Excel File. The Idea is to Update The description field

I want to see

 Col A    Col B     Col C       Col D         Col E
 Item ID  Item ID   Item Desc   Purchase D    Sales Des
 600255    600255   mmmm        nnnn          yyyy
 600224    600224   aaaa        bbbb          ccc
 600111    600111   xxxx        yyyy          zzzz

Upvotes: 0

Views: 82

Answers (1)

B540Glenn
B540Glenn

Reputation: 409

Try using the OFFSET() function.

In the file where you want to update the descriptions enter the following array formula to the cells you want to hold the description fields. This will take the offset of rows and columns from your source to match to your ItemID.

{=OFFSET(Sheet1!A1:D1,(MATCH(Sheet2!A2,Sheet1!A1:A5,0)-1), 0, 1, 4)}
Highlight 4 contiguous cells in the row then     
Press Ctrl+Shift+Enter to create the array formula

Of course you will have to correct for the workbook and worksheet names, along with the cell locations.

Sheet1 A1:D5 is the location of your Item Description data.

Sheet2 A2 is the ItemID that needs more data.

Sheet1 A1:A5 is the list of ItemIDs that have descriptions

(MATCH(Sheet2!A2,Sheet1!A1:A5,0)-1) will get the row of the description data relative to zero (important for the offset).

0, tells the offset function to stay on that row.

1, tells the offset function to select 1 row.

4 tells the Offset function to select 4 columns.

Upvotes: 1

Related Questions