Match & Determine Frequency

Hi,

I have 4 Excel files with a large list of IDs in them
-- Table 1 is organized by sales
-- Table 2 is organized by known products
-- Table 3 is QAs list of known product IDs
-- Table 4 is table that a department is working

I need to compare these files, match up product IDs & verify which list is more valid or confirm the product IDs that are in actual used.

After verifying Product IDs usage, looking to do the following:
-- Develop some type of "Match" verifification between 2 or all the
columns or files, which will be compliled into 1 worksheet
-- Determine frequency of how many times an ID matches between
all fo the tables or files
-- Perhaps, priortize product IDs by the frequency that the IDs
shows up


I attached a sample file


Thanks

Hi,

I haven't decided if I want to or if I can do this yet. Perhaps, I want to use one of the Excel files, such as the Sales files or IDs to be the master table & compare all others to this table.

If possible, I may want some function to generate the list for me. How would this be done?

First priority is to match up the IDs & determine product IDs usage.

Thanks

im a little hazey on exactly what you are trying to achieve, where you want the answer to appear, and what format you wantthat answer to be in. perhaps the sample file you supplied is a little small. also, it may be easdier to help if you'r file showed your desired outcome and how you arrived at it, to give a better idea on how to help you

Hi FDibbins,

I'm just getting into this myself for a project that I need to do. I modified the original file that I hope provides some additional clarification towards my objectives.


I think to begin let's try to match 2 of the tables. Ultimately though, I will need to match all 4 tables or columns. I would like a function that can go through 2 or 4 of the columns, match up the Product IDs, then supply those results in another column. I think once I have that, I can determine the frequency.

My first & primary objective is to determine that these Products are being used within our system. After this is determined, I may need to do some other things, but at the moment, I don't have Excel related questions yet for these.

Thanks

Hi Group,

Perhaps, this would be easier for someone to answer. Based off the most recent Excel file example submitted, please provide a few "match" function examples that can 'match' the range of data between 2 of the columns or better yet, all 4 of the Product ID columns.

Thanks

Hi Group,

Can anyone assist with providing "match" functions between 2 or 4 columns / tables of data & provide results of the (product) IDs that "match up"?

See latest example table submitted .....

Thanks

Hi Group,

If I have 2 columns of alpha-numeric data & want to "match" contents between the 2 columns, then provide the result of the matches, what would be a good function? I'm also trying to determine the frequency or appearances between the 2 columns or tables. Once I get the match going, I think I can determine the rest of what I need.

Thanks

Hi Group,

I have this function: =IF(COUNTIF(SM_List,DC_TPT_List),INDEX(SM_List,MATCH(DC_TPT_List,SM_List,0)),"No Match") that partially works.

In my column lists, I have alpha-numeric & numeric IDs. It appears that this funciton is finding the alpha-numeric IDs alright between the 2 columns, but it's not finding all the numeric IDs.

As for the named ranges, you can replace them with a sample cell range.

How can I fix this function or develop a new one to assure that the IDs are being "matched up" between the 2 columns?

Thanks

There are no named ranges in your workbook so people would have a hard time guessing what that does. Why not put examples of the outputs you want, and someone may suggest a formula to implement them?

Hi,

Attached is an updated, Match & Frequency.xlsx file with the, =IF(COUNTIF(SM_List,DC_TPT_List),INDEX(SM_List,MATCH(DC_TPT_List,SM_List,0)),"No Match") function in it.
Review function in k15:k21

As stated within the file, my Master file function, which is the same function as above, but much larger ranges, is not "matching" up all the numeric IDs. I need a function that assures me that all IDs are being matched up between 2 columns of data & perhaps later, 4 or more columns of data.

Thanks

In K15 and copy down,

=IFERROR(INDEX(SM_List, MATCH(E15, DC_TPT_List, 0)), "No Match")

Hi Shg & Others

The =IFERROR(INDEX(SM_List, MATCH(E15, DC_TPT_List, 0)), "No Match") is giving an incorrect result. It appears it's providing the data found in b15 through b21 when in reality, there's not a mach for all the IDs between the 2 columns.


For example, MNDJ17 is only in column B & not C & therefore, there is no match.

Please advise on how to fix or assure that IDs found in column E "matches" or does not match - "No Match" IDs found in column B, regardless if it's alpha-numeric or numeric data.

Thanks

hi mycon73,

is the problem of incorrect results in the sample file data from post #10 or in your actual file? is it possible that some of the seemingly numerical IDs are actually numbers stored as text in your actual file?

just to ascertain / eliminate the 'text' issue, try this in K15 and drag-fill down:

=IF(ISNUMBER(MATCH(TRIM(E15&""),INDEX(TRIM(SM_List&""),0),0)),E15,"No Match")

Hi icestationzbra,

I did check to determine if some cells are being read as text vs. numbers. As far as I can see, all the cells are being read as numbers. Jut to reverify, I set the cells to numbers.


Going to go try your formula now...

hi mycon73,

the easiest way to figure out if numbers are masquerading as text is to remove any sort of alignment (left, centre, right) applied to those cells. at that point, if numbers appear left justified, they are actually text. sometimes, applying "number" format does not help and some other trick (such as Paste Special > Multiply) has to be attempted.

Hi Hi icestationzbra & Others,

I checked the spacing of the cells that "not matching" & find it quite interesting the function used cannot find the match, but a simple Control F or Find command can. At the moment, I cannot find why the function is not finding certain matches.

I attached a sample file & highlighted some of the areas for review - See columns T & U - I have 2 different functions that's suppose to do the same thing.

Thanks

mycon73,

you have used neither the formula that @shg provided, nor the one that i provided. why don't you try one or both of those and see what the results are?

per your workbook in the previous post, my formula can be updated to:

=IF(ISNUMBER(MATCH(TRIM(C14&""),INDEX(TRIM(DC_TPT_List&""),0),0)),C14,"No Match")

start in, say, Z14 and drag-fill down.

Hi icestationzbra & Others,

Now, I have 3 different functions, =IF(ISNUMBER(MATCH(TRIM(C14&""),INDEX(TRIM(DC_TPT_List&""),0),0)),C14,"No Match") being the most recent.

In K35, there is an ID of 707213 that neither of the functions is finding in column C. Yet, when conduct a Control F or Find, I see ID 707213 in cell 293.

Why can't anyone of these functions find this ID or similar ones? As stated previously, I checked the cell type & for extra spacing & both cells is set to number with no additional spaces.

Overall, any one of these find or match functions seems to work well, but there are still a few incidents in which it is not. I need a function that's 100% accurate.

Please advise....

Thanks


Hi icestationzbra & Others,

this formula that you most currently stated as having tried matches a value in column C against the values in column K and if there is a match, it outputs the value in column C, else it presents "No Match".

let us say you placed the above formula in V14 and drag-filled all the way down to V302. now, the value in question - 707213, which is present in cell C291, is indeed being matched. if you look across to the result presented by the formula in cell V291, you will find that it is NOT "No Match".

so, the question is, what is your expectation? when a value in column C is matched against those in column K, and a match is found, what would you like the result to be in column V? would you like the value from column C to be presented, or the value from column K?

Hi,

I believe I need to conduct a vertical lookup & match & then I should be able to get desired or expected results. How can I modify or utilize another function to do this?

Thanks

if you share explicit requirements, i will try my best to help...

Hi icestationzbra,

You've been very helpful already!

As pointed out through these discussions or within the Excel sample files, I need to compare a few "master" (product) ID lists which came from 4 different files. I copied or compiled these ID lists to one file. For the example file I gave, I only gave 2 ID list columns are the first 2 are the ones being focused on first. Will eventually need for all.


In theory, between all my ID lists, the majority of IDs should match up (vertically somewhere within its respective columns). If the IDs match up, then I know I can work those product IDs. If there is not a match between 2 or all of the columns, then I need to investigate why, which I will do.

Right now, it appears that I have a function that does 1/2 of what I want or need, but not fully functioning or providing 100% "match up" results.

I think the Vlookup & match function would be ideal for this scenario, but I can't get to work. This is where I need yours or others expertise to provide a function that can meet my scenario (through the latest example files given)

Thanks again...