Hi Excel Experts,
I need some help creating VBA code that will accomplish a very repetitive task.
I have 2 sheets of data, I need to compare Sheet 1 to a specific range on Sheet 2 and where a value from that range on Sheet 2 appears in the PIO String (column 3) it needs to be removed from the string.
On sheet 2 there are 3 rows of headers that denote the series, code and description, these are for reference only and should not be checked against.
The challenge I have is Columns 1 and 2 need to be used as a reference for which list of values to check against on sheet 2. Column 2 is always 6 characters in length but the comparison only needs to be done against the first 4 characters since that is the layout on Sheet 2
Sheet 1
<tbody>
</tbody>
Sheet 2: The sheet is 12 columns wide and up to 46 rows long.
<tbody>
</tbody>
In this example, Row 2 on Sheet 1 would be compared to Column 1 on Sheet 2 because they share the same series and first 4 characters of the PIO code. The result would be that Row 2 needs to be altered because RB5220 is present in the corresponding list on Sheet 2 the same goes for Row 4 for RB1100.
The formatting of the end result is sensitive also, Sheet 1 Column 3 needs to maintain a single space between each item.
I hope this comes across clearly, I am happy to clarify further.
As always, thank you in advance for your help.
I need some help creating VBA code that will accomplish a very repetitive task.
I have 2 sheets of data, I need to compare Sheet 1 to a specific range on Sheet 2 and where a value from that range on Sheet 2 appears in the PIO String (column 3) it needs to be removed from the string.
On sheet 2 there are 3 rows of headers that denote the series, code and description, these are for reference only and should not be checked against.
The challenge I have is Columns 1 and 2 need to be used as a reference for which list of values to check against on sheet 2. Column 2 is always 6 characters in length but the comparison only needs to be done against the first 4 characters since that is the layout on Sheet 2
Sheet 1
Series | PIO | PIO STRING | |
<tbody> </tbody> | XX1000 | CY2000 FP5000 MC2000 PF1000 RB5220 UF60 XY9000 | |
<tbody> </tbody> | XX1110 | CY2000 FP5000 MR1000 PF1000 UF60 XY9000 | |
<tbody> </tbody> | XX1500 | BS1000 CY2000 FP5000 LL1000 MR1000 PF1000 RB1100 UF60 XY9000 | |
<tbody> </tbody> | XX1020 | CY2000 FP5010 MC1000 PF1000 UF60 XY9000 | |
<tbody> </tbody> | XX2000 | FP4000 MR1000 PF1000 UF60 XY9000 | |
<tbody> </tbody> | XX2000 | FP5000 JB2300 MR1000 UF60 | |
<tbody> </tbody> | XX1300 | BS1000 BU1000 FP5000 UF60 XY9000 | |
<tbody> </tbody> | XX1000 | FP4000 MC2010 PF1000 UF60 XY9000 | |
<tbody> </tbody> | XX1020 | FP4010 MC1000 UF60 XY9000 |
<tbody>
</tbody>
Sheet 2: The sheet is 12 columns wide and up to 46 rows long.
4Runner | Tundra |
XX10 | XX15 |
Wheel Package | Wheel Package |
AC1000 | AA2800 |
AJ1700 | AC1000 |
BG2000 | RB1100 |
RB5220 | ET1100 |
SBXP10 | AL6000 |
<tbody>
</tbody>
In this example, Row 2 on Sheet 1 would be compared to Column 1 on Sheet 2 because they share the same series and first 4 characters of the PIO code. The result would be that Row 2 needs to be altered because RB5220 is present in the corresponding list on Sheet 2 the same goes for Row 4 for RB1100.
The formatting of the end result is sensitive also, Sheet 1 Column 3 needs to maintain a single space between each item.
I hope this comes across clearly, I am happy to clarify further.
As always, thank you in advance for your help.