compare (excel 2003)

Topic

New Reply

Greetings,

From the attache file, I like to compare the LIST in cell A against cell C and if there is a match, I want to take the number from the COUNT column (cell D), copy them into cell B, and add them to get one total.
What is the best way to accomplish this?

Thank you in advance!

Reply | Quote

Replies

The data are problematic, since there are trailing spaces after the text values. And I assume that A & T should match A&T, but they don’t.

A possible solution using formulas only is demonstrated in the attached .

Reply | Quote

Thanks Hans for the reply post.
The excel file I get from the client monthly is about 400 records from which I have to filter out only needed group & count (about 300 or so records). I attached another file to better explain the process. In the attached file, the tab

Reply | Quote

The attached version contains some VBA code to clean up the names and to create the formulas for you. The names of the worksheets are specified near the top of the module. Run the macro Compare to regenerate the formulas (it has already been donw on the attached version).

Reply | Quote

Hans,
Sorry for the delay in getting back..
I’ve applied your method & more or less it works fine except where there is inconsistent data entry.
In the attached (little modified) workbook some of the data entry are not consistent:
spacing of words, abbreviations etc. For example, A&T vs. A & T, A+ MODELING vs. A+ MODELING INC. etc.

1.How does this affect my data integrity and result as i have about 500 records? Can excel’s LEFT function be used or is there other way around this?

2. From the previous post what exactly does the following line of code?
mt = Worksheets(MyList).Range(“A65536”).End(xlUp).Row

Thank you in advance!
OCM

Reply | Quote

1. You should either get the master list to use the names from “your” list or the other way round, otherwise it’ll be virtually impossible to get all matches.

2. Range(“A65536”) is the last (bottommost) cell in column A in Excel 2003 (in Excel 2007 there are many more rows).
End(xlUp) moves up from this cell until a non-blank cell is encountered.
Row returns the row number of this cell.
So mt is the row number of the last non-blank cell in column A.

Reply | Quote

You can try some NearMatch functions that I created. This will work with limited numbers, but you will have to at least replace all the spaces if the spaces are not important

Steve

Reply | Quote

Hans/Steve,
Thank you both for your reply post.

Regards,
OCM

Reply | Quote