Hi all,
I have a two columns of text which I need to make sure both match as the data comes from different sources. Both columns of text have characters in the text that can throw off the match formula as being an error, example below:
The name match says no to one of them because there is a - in the document name.
So I add two hidden columns on the end (G and H) - see below
In Column D, I have the match formula, but still have No error.
In Column G, my formula is =IF($A4="","",SUBSTITUTE(VLOOKUP($A4,$A:$C,2,FALSE)," -"," "))
In Column H, my forumla is =IF($E4="","",SUBSTITUTE(VLOOKUP($E4,$E:$F,2,FALSE)," - "," "))
This works fine, but there examples where the '-' will have a space between that at the next letter; example: SITEWIDE - KEY PLAN, other times it will be as SITEWIDE -KEY PLAN (no space).
So the problem I have is that I want to be able to make my IF formula do carry out the SUBSTITUTE formula based on both conditions and with the end result being this SITEWIDE KEY PLAN so the name match formula end result will say 'Yes'.
There would be text where there is more than one '-' character in the text.
Any help would be great.
The end result would be that the '-' would be omitted and there would be just one normal space between words.
Wayne
I have a two columns of text which I need to make sure both match as the data comes from different sources. Both columns of text have characters in the text that can throw off the match formula as being an error, example below:
The name match says no to one of them because there is a - in the document name.
| A | B | C | D | E | F |
| Sheet Number | Sheet Name | Current Revision Issued | Name match | Document number | Document name |
| BHE-0001 | GENERAL NOTES SHEET 01 | P03 | yes | BHE-0001 | General Notes - Sheet 01 |
| BHE-0002 | GENERAL NOTES SHEET 02 | P03 | yes | BHE-0002 | General Notes - Sheet 02 |
| BHE-0100 | SITEWIDE -KEY PLAN | P03 | no | BHE-0100 | SiteWide - Key Plan |
So I add two hidden columns on the end (G and H) - see below
In Column D, I have the match formula, but still have No error.
In Column G, my formula is =IF($A4="","",SUBSTITUTE(VLOOKUP($A4,$A:$C,2,FALSE)," -"," "))
In Column H, my forumla is =IF($E4="","",SUBSTITUTE(VLOOKUP($E4,$E:$F,2,FALSE)," - "," "))
This works fine, but there examples where the '-' will have a space between that at the next letter; example: SITEWIDE - KEY PLAN, other times it will be as SITEWIDE -KEY PLAN (no space).
So the problem I have is that I want to be able to make my IF formula do carry out the SUBSTITUTE formula based on both conditions and with the end result being this SITEWIDE KEY PLAN so the name match formula end result will say 'Yes'.
There would be text where there is more than one '-' character in the text.
| A | B | C | D | E | F | G | H | |
| 1 | Sheet Number | Sheet Name | Current Revision Issued | Name match | Document number | Document name | revit | issue sheet |
| 2 | BHE-0001 | GENERAL NOTES SHEET 01 | P03 | yes | BHE-0001 | General Notes - Sheet 01 | GENERAL NOTES SHEET 01 | General Notes Sheet 01 |
| 3 | BHE-0002 | GENERAL NOTES SHEET 02 | P03 | yes | BHE-0002 | General Notes - Sheet 02 | GENERAL NOTES SHEET 02 | General Notes Sheet 02 |
| 4 | BHE-0100 | SITEWIDE -KEY PLAN | P03 | no | BHE-0100 | SiteWide - Key Plan | SITEWIDE KEY PLAN | SiteWide Key Plan |
| 5 | BHE-0121 | STRUCTURAL 3D VIEWS SHEET 01 | P03 | yes | BHE-0121 | Structural 3D Views - Sheet 01 | STRUCTURAL 3D VIEWS SHEET 01 | Structural 3D Views Sheet 01 |
| 6 | BHE-0122 | STRUCTURAL 3D VIEWS SHEET 02 | P03 | yes | BHE-0122 | Structural 3D Views - Sheet 02 | STRUCTURAL 3D VIEWS SHEET 02 | Structural 3D Views Sheet 02 |
Any help would be great.
The end result would be that the '-' would be omitted and there would be just one normal space between words.
Wayne
