Oct 04 2020 05:00 PM
Hi,
I'm trying to find and understand the most appropriate formulas for finding and displaying a series of partial text strings and subsequently, the dollar values associated with them to separate worksheets from two data sources. The first screen shot displays example data from the first data source. The second screen shot displays example data from the second data source. I need to match the project names contained within these two data sources (and their values) to another worksheet to complete a comparison of the cost values.
Hope this explanation is clear - please messge me if you need more information.
Thanks
Oct 04 2020 05:43 PM
Oct 04 2020 05:50 PM
Hi ,@JMB17
Thanks for that. I did quite a bit of reading prior to posting around the 'limitations' of vlookup text strings and the need to potentially use the fuzzy match. I would've thought that there was another way to simply find and display the text from one worksheet to another. For example, the projects all have the BBR prefix - I thought it was possible to find and display any text containing 'BBR' to display in the comparison worksheet that I've created?
Cheers
Oct 04 2020 06:27 PM
Oct 04 2020 10:56 PM - edited Oct 04 2020 11:02 PM
@jwobrien ,,
Write me some sample text string to math Like Unit Rate sheet has column 1 with values Engineering ID,,, and others and sheet Estimate has BBR ,,, what you want from both sheet as partial match ,, give some example then I'll show U FORMULA to pull records!!
Meanwhile you may try this :
{=IFERROR(INDEX($A$48:$C$51,SMALL(IF(COUNTIF($A$48:$A$51,"*"&$A$53&"*")*COUNTIF($B$48:$B$51,$B$53),ROW($A$32:$C$37)-MIN(ROW($A$32:$C$37))+1),ROW(A1)),COLUMN(A1)),"")}
N.B.
Oct 05 2020 04:19 PM
Sorry - My explanation wasn't clear. I have 3 data sources (1 from Primavera P6 and 2 excel files) - all contain the same projects with differing estimated project $ values as well as the detailed data of how the estimates are built up. My task is to compare the three estimated $ values and understand where they vary. The project nomenclature varies across the three data sources but the product name prefix is consistent i.e. BBR as is the kilometre start and finish markers - so those are the unique identifiers. The first task is to display the project names from the three data sources on a separate worksheet, firstly to confirm that the numbers of projects match (scope can be removed/changed at short notice and it isn't communicated well to the owners of the three data sources).
Hope that make more sense.