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.
Does anyone know how to approach this? Any assistance is greatly appreciated.
20040452B-ME-LST-0001_A - Copy.xlsm | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | ID # | ID # Combined | Type | ||
2 | MR-9001 | MR-9001/1 | Skid | ||
3 | MR-9002 | MR-9002 | Wheel | ||
4 | VFS-1001 | VFS-1001 | Light | ||
5 | H-5051A | H-5051A/B/C/D/E | Tank | ||
6 | HST-1001 | HST-1001 | Feed | ||
7 | VPC-1001 | VPC-1001 | Bay | ||
8 | VPC-1002 | VPC-1002 | Jack | ||
9 | H-5051B | Tank | |||
10 | H-5051C | Tank | |||
11 | H-5051D | Tank | |||
12 | H-5051E | Tank | |||
13 | MR-9001 | Skid | |||
14 | HAC-5012 | HAC-5012 | Trimmer | ||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B4,B9:B14,B6:B7 | B4 | =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 | ||
---|---|---|
Name | Refers To | Cells |
_FilterDatabase | =Sheet2!$A$2:$C$14 | B6:B7, B9:B14, B4 |