Combining multiple excel sheets corresponding to name

Occasional Contributor

Hello All,

Looking for some help here. I have a large excel sheet with multiple names that repeat - simplified version here. This is my Sheet 2

 

NameCat 1Cat 2Cat3Cat4Cat 5Cat 6
M10000000
M6000001
M6000001
M8000010
M9010000
M9000010
M10103060
M6124060
M6124060
M81011900
M8103300
M9212200
M10010000
M10000001

 

Sheet 1 is arranged in this fashion. It has the first occurrence of the names in sheet 2

 

M6000001
M8000010
M9010000
M10000000

 

I would like to use a code to automatically add the other occurrences added under the name category.  Please note some repeat multiple times and some don't repeat at all.

 

Expected output:

NameCat 1Cat 2Cat3Cat4Cat 5Cat 6
M6000001
M6000001
M6124060
M6124060
M8000010
M81011900
M8103300
M9010000
M9000010
M9212200
M10000000
M10103060
M10010000
M10000001

 

1 Reply

@giriokamat Try SORTBY() function.

=SORTBY(Sheet2!A2:G15,--SUBSTITUTE(Sheet2!A2:A15,"M",""))

Harun24HR_0-1663170716030.png