Combine cells based on ending and intermediate values.

Ralph1024

New Member
Joined
Apr 22, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I am looking for a way to combine cells based on similar values.
For example, in the following screenshot, if column A has a similar ending value 64B-20H2"A" and 64B-20H2"B", these would combine into 64B-20-H2A/B (column B) as long as their names match in column C.

I am now looking to see if there is a way to combine rows 12-17 so that it reads like the second screenshot. The current formula in column B combines based on the last character of the Column A and a matching name in Column C.

Does anyone have any have an idea on how to pursue this? Any feedback is greatly appreciated.


Screenshot 1
1623257642045.png


Screenshot 2
1623257647718.png


Combining Cells.xlsx
ABC
1IDCombined ID'sName
264B-20-H2A64B-20-H2A/BHeater
364B-20-H2B Heater
464B-20-P3A64B-20-P3A/BPump
564B-20-P3B Pump
664B-20-PM3A64B-20-PM3A/BPump Motor
764B-20-PM3B Pump Motor
864B-20-Z2-P1A64B-20-Z2-P1A/BWESP
964B-20-Z2-P1B WESP
1064B-20-Z2-PM1A64B-20-Z2-PM1A/BWESP Recycle
1164B-20-Z2-PM1B WESP Recycle
1264E-20A-M164E-20A-M1/2Fan Motor
1364E-20A-M2 Fan Motor
1464E-20B-M164E-20B-M1/2Fan Motor
1564E-20B-M2 Fan Motor
1664E-20C-M164E-20C-M1/2Fan Motor
1764E-20C-M2 Fan Motor
Sheet1
Cell Formulas
RangeFormula
B2:B17B2=IF(ROWS(B$2:B2)=MATCH(LEFT(A2,LEN(A2)-1)&"|"&C2,LEFT(A$2:A$17,LEN(A$2:A$17)-1)&"|"&C$2:C$17,),LEFT(A2,LEN(A2)-1)&TEXTJOIN("/",,FILTER(RIGHT(A$2:A$17,1),(LEFT(A$2:A$17,LEN(A$2:A$17)-1)=LEFT(A2,LEN(A2)-1))*(C$2:C$17=C2))),"")
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,141,062
Messages
5,704,057
Members
421,325
Latest member
tapete86

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top