COUNTIF? Help needed please - I have been working on this for two hours with no luck

Hi there,

I have a workbook with two tabs.

Each tab has a table with four colums and the first column is a code. the first tab has codes for all the updated pipe products in UK and the second tab is my old copy of the product codes for my area.

View attachment PIpe_value.xlsx

In short I need to update any codes for my area that have different values associated with them in the updated version. So what I would ideally like to do is search the UK tab(1st tab) to find the codes I have in the South East (tab 2) and if the values in the cells associated with it are different, to show the code and it values for that product and the code and values I have for it, any codes that match with no differences I do not need to appear.

My real database has thousands of records. So I need a way to find just the relatively few that have been updated and just show them, so I can change them on my area worksheet.

I am really stuck here, but it's probably relatively easy to do if you know Excel.

Any help would be much appreciated. I'm a complete novice so any instructions may need to be basic.

Thanks,
K

Perhaps like this ?View attachment PIpe_value.xlsx

Now you have different options :
- leave as is
- hide the columns with the old values
- copy - paste special - values the new values over the old one
- the easiest way ( but long to explain) : first backup to be sure - select all the new values at once and place the mouse cursor against the upper limit (it becomes crossed arrow) of the selection - hold down the right mouse button and WHILE HOLDING IT DOWN move the selection to the desired place - release the button - a menu opens - select " paste here as values only" - Done

Hi there,

Thank you the quick reply Pecoflyer.

This is actually just an example of the database, so I would like some advice of how to do it myself, step-by-step or link to info on a website if possible.

I hope I was clear in my question. I just need to show the rows from each tab one below the other, so that I can easily see ONLY the code rows that have different values (in any cell) which is associated with the code. SOme I imagine will not have changed at all. But they may be slightly different,

e.g. code pipe volts pipe no. pipe usage pipe dim
UK 12454 5 2 54 5
South East 12454 4 2 2 2

Even if just one column cell for that record(row) is different. I can then change my South East tab with updated info from UK (latest) tab.

Forgive me if I am repeating myself, I've been at this for a while and getting pretty tired.

If there is a better way, then please advise, but I will need to understand process of how to get result as I will be working on seperate database.

Thanks again,
K

Hello again Pecoflyer,

I've had a chance to look at what you did for me again and I can see it's just what I'm looking to do. What a relief. Sorry if last post was confusing. Please ignore.

Pecoflyer, would you be able to let me know how you did to get the result like this please? Just need to know how to do it so I can carry out the steps on other database(i'm a beginner at this).

Thank you in advance,
K

Among many others, here is one site with a good explanation http://www.contextures.com/xlFunctions03.html

You could achieve the same with the VLOOKUP, but the iNDEX/MATCH combination is much more flexible ( and faster for large ranges)

Thank you for the link Pecoflyer. I have read through the website and looked at relevant videos but I'm still struggling to understand how you carry out this procedure step by step. I've tried to break down the function formula but I can't seem to replicate it when I try it myself.

Please could you let me know the steps you took to complete this task. I need to know how to do this by tomorrow so it would be really helpful.

Thank you for your time, I appreciate it.

K

Pecoflyer, you've helped such a lot, thank you.

I just need to know how you did it, in simple instructions please. Without it, I'm still not able to do it.

I look forward to hearing from you,
Kind regards,
Keith

Use the Evaluate Formula feature in Excel to step through the formula to understand what it is doing.

Thank you for your help. I've looked into it, but still not sure what Pecoflyer did to get to the stage the formula is at in PIpe_value.xlsx (see post no.2 if link doesn't work).

Please can anyone enlighten me as to how to do this?

Thanks,
K

Hi AliGW or other Excelguru members,

Do you know how to carry out the excel procedure (which I have asked above) to find a number of records from one list (South East) and find them in another (Code Sheeet UK) and extract to show in another area of spreadhseet only if the item code matches and one or more of the cells on that row have a different value.

Please can somebody help? I thought this forum would be able to help, I'm finding it quite hard and I have no idea how to understand the process. I am grateful for people's time.

Please spare the time to help me on this? It's for my work and they will be expecting me to know it.

All the best,
Keith

I think the formula used by Pecoflyer doesn't need the IF() check. Just the simple INDEX/MATCH function will retrieve the same results.

So use this formula in F2:

=INDEX('Code Sheet UK'!$A$1:$E$34,MATCH($A2,'Code Sheet UK'!$A$1:$A$34,0),MATCH(F$1,'Code Sheet UK'!$A$1:$E$1,0))

copied down and across.

The syntax of the INDEX function is INDEX(array,row number,[column number])

this will retrieve the value from an array at the intersect of row number and optionally column number.

The array is the range to data is in. The row number is gotten using the MATCH() function which has syntax MATCH(lookup_value, lookup_array, [match type]) and returns a numeric result which is the position within the lookup array that the lookup value is found (you need a match type of 0 for exact match).
The same MATCH() function can be used in a horizontal lookup array to find the corresponding position or column number. So you would use the MATCH() functions on the one-dimension row and column headers to get the vertical and horizontal positions of the matches. The intersection of these two is the INDEX value to return as the final result.

Not sure if that clarifies it for you a bit more.

More resources to under Index/Match combination: https://www.google.ca/search?q=index+match&ie=utf-8&oe=utf-8&gws_rd=cr&ei=mWH-WJCUGYavjwTVsavgBg

Please do not ask several questions in the same thread. Create a new thread ( with a meaningful title) for each news question, eventually adding a link to another relevant thread.