# Combine/Merge Cells with similar values

#### Ralph1024

##### New Member
I am trying to combine cells whose values vary by one character and match a certain reference. In my example, B2 should read MR-9001/1 since there are 2 skids and cell B5 should read H-5051A/B/C/E since there are 5 Tanks. B9 through B13 should be empty since they have already been found. B3, B4, B6, B7, and B8. Should have the ID #'s from column A since those were not found as duplicates elsewhere. Some of the cells in column B have a formula thanks to the help of others.

Does anyone know how to approach this? Any assistance is greatly appreciated.

20040452B-ME-LST-0001_A - Copy.xlsm
ABC
1ID #ID # CombinedType
2MR-9001MR-9001/1Skid
3MR-9002MR-9002Wheel
4VFS-1001VFS-1001Light
5H-5051AH-5051A/B/C/D/ETank
6HST-1001HST-1001Feed
7VPC-1001VPC-1001Bay
8VPC-1002VPC-1002Jack
9H-5051B Tank
10H-5051C Tank
11H-5051D Tank
12H-5051E Tank
13MR-9001 Skid
14HAC-5012HAC-5012Trimmer
Sheet2
Cell Formulas
RangeFormula
B4,B9:B14,B6:B7B4=IF(COUNTIF(A\$2:A4,LEFT(A4,LEN(A4)-1)&"?")>1,"",IF(C4=C5,TEXTJOIN("/",,A4,FILTER(RIGHT(A5:A16,1),IFERROR(LEFT(A5:A16,LEN(A5:A16)-1)=LEFT(A4,LEN(A4)-1),0),"NO")),A4))
Named Ranges
NameRefers ToCells
_FilterDatabase=Sheet2!\$A\$2:\$C\$14B6:B7, B9:B14, B4

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### StephenCrump

##### MrExcel MVP
Try:

B2: =IF(ROWS(B\$2:B2)=MATCH(LEFT(A2,LEN(A2)-1)&"|"&C2,LEFT(A\$2:A\$14,LEN(A\$2:A\$14)-1)&"|"&C\$2:C\$14,),LEFT(A2,LEN(A2)-1)&TEXTJOIN("/",,FILTER(RIGHT(A\$2:A\$14,1),(LEFT(A\$2:A\$14,LEN(A\$2:A\$14)-1)=LEFT(A2,LEN(A2)-1))*(C\$2:C\$14=C2))),"")

#### Ralph1024

##### New Member
Try:

B2: =IF(ROWS(B\$2:B2)=MATCH(LEFT(A2,LEN(A2)-1)&"|"&C2,LEFT(A\$2:A\$14,LEN(A\$2:A\$14)-1)&"|"&C\$2:C\$14,),LEFT(A2,LEN(A2)-1)&TEXTJOIN("/",,FILTER(RIGHT(A\$2:A\$14,1),(LEFT(A\$2:A\$14,LEN(A\$2:A\$14)-1)=LEFT(A2,LEN(A2)-1))*(C\$2:C\$14=C2))),"")
StephenCrump, Thank you! This just saved me loads of time.

Replies
0
Views
97
Replies
13
Views
361
Replies
5
Views
162
Replies
8
Views
129
Replies
3
Views
69

1,141,400
Messages
5,706,217
Members
421,433
Latest member
yash0468

### 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.

### Which adblocker are you using?

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

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