July 28, 2020
I hope you can help with this problem, I am using Excel for Office 365.
I have a list of ~1,000 alphanumeric codes along with descriptions which should be the same for each of the core 5 digit part of the Code. So 40008 and 40008C should both have the same description, the 5 digit code is the master and any alpha suffix Codes should have the same description. In the Match column I need a formula that looks at the helper column Base (which I added for just the first 5 digits of the Code) and, where they are the same, check if the Descriptions are also the same (case insensitive), marking all the rows for that Base code. I have used TRUE and FALSE but it could be anything as I am just using it to filter a pivot table. If there is only one item for the base then it will be TRUE since there is nothing to compare it with. Where there are >2 Codes for the base e.g. 40008, then even though one is the same as the Code master they are all marked as FALSE because one of them is. A sample looks like this, Excel file attached.
Code Description Base Match
40007 Desks 40007 TRUE
40008 Chair 40008 FALSE
40008C Chair w arms 40008 FALSE
40008D Chair 40008 FALSE
40015 Lamp 40015 TRUE
40015D LAMP 40015 TRUE
Thanks in advance!