[deaexcelpadawan]

Hi all,

I hope everyone is well! I really need some help with an excel formula if anyone has the time it would be much appreciated. I've been working on this for a while and I'm not even sure which formula I should be using let alone which format it should be written it.

I've been looking through past threads and I can't find what I need to do anywhere. There was one formula that came close:

=SUMPRODUCT((A2:A100="Aimbry")*(B2:B100="LW")*(C2: C100))

but this adds up all the matching results.

What I need to do is match the info in columns A and B and return a number in column C.
The problem is that there are multiple rows with identical entries and I just want to return one of them.
So I was thinking a version of the above that produced a mode or average would give the desired result.

It would be so helpful if someone could give me a point in the right direction, I've been pulling my hair out over this for a while now! Lol!

Thank you for you time

[deaexcelpadawan]

Cheeky Charlie
Registered User of Microsoft Excel Tips from Excel Tip .com / Excel Tutorial / Free Excel Help


Make a column D, D2=SUMPRODUCT(N(A$2:A2&B$2:B2=A2&B2))

Copy this down, it will count each unique entry across A & B - then you can amend your formula -
=SUMPRODUCT((A2:A100="Aimbry")*(B2:B100="LW")*(C2: C100)*(D2:D100=1))

to give the first instance of each unique entry - changing the bold 1 to a two will only sum the second instance of each etc.

There may be better ways, this is one approach.


Thanks again to Cheeky Charlie for helping me out me!

[ScottGem]

I'm not sure I understand, Can you give us an example of what your spreadsheet looks like? What are you trying to match, etc.

If you want you can attach a sample file to your psot here.

[JBeaucaire]

Can you post a link to the conversation you had over there? As I understand your question, this result doesn't do what you asked. So, was the question mispresented, or is the explanation you've provided in the solution mispresented?

I'd like to know.

[deaexcelpadawan]

HI guys

Sorry for taking ages to get back to you!
I have prepared an example for you to look at which is attached.

There's a few things going on so take you time... let me know if you have anymore problems

[ScottGem]

You need to describe in greater detail what you want to do with this sheet. Which are the columns (A, B, & C) that you merntioned in your OP?

[deaexcelpadawan]

Hi guys

I've described each formula and each part of the formula that I understand, in the excel file. Have a read.

You can tell which columns Im copying from the working tab to the summary tab by looking at the column titles.

I hope this helps... Good Luck!

[ScottGem]

Ok, I think I'm beginning to understand. You want to look up a supplier and return the values from the numerical columns. So the question is how do you determine what row to choose from?

[deaexcelpadawan]

Lol yes I guess that would have been a simple way of putting it, when I wrote this I didn't know how to put several criteria into a formula so I prob should have done it in two threads... do you think I need to change how the question is worded?

Do you think the question is answered in an understandable way now?

Cheers

[ScottGem]

But you haven't answered my question about how you determine which row?

[deaexcelpadawan]

oohh, OK soz


The formula below is in column F on the Work tab
=SUMPRODUCT(N(A$2:A2&B$2:B2=A2&B2))

Im not sure how this formula works but
This formula looks at the info in column A and B, then looks to see if it matches anything that came in to columns before it. Therefore the first unique row will have a 1 in it and every copied row after that gets a higher number… SO to make this formula work copy into to cell and copy down the row.
This formula works by only looking in Column A and B in the rows that are above the current row and sees if the currents rows match any that came before it.

On the summary tab
Here is an example of the formula

=SUMPRODUCT((WORK!$A$2:$A$55=A5)*(WORK!$B$2:$B$55= $A$3)*(WORK!$C$2:$C$55)*(WORK!$F$2:$F$55=1))


The last bracketed bit which I've copied below is the bit that confines the search to one row
(WORK!$F$2:$F$55=1))
This means look in the work tab in column F and find the number 1



PS. Here's a copy of my Explanation contained in the spreadsheet of what the different parts of the formula do


SUMPRODUCT((WORK!$A$2:$A$55=A5)
This means look in the work tab in column A and find the value shown on the current spreadsheet in cell A5


(WORK!$B$2:$B$55=$A$3)
This means look in the work tab in column B and find the value shown on the current spreadsheet in cell A3

(WORK!$C$2:$C$55)
The three other parts of this formula are designed to whittle down the options on the working tab so only one row is unique to these specifications and this formula just says copy the value in column C that is in the row that matches the criteria of the other parts of the formula

(WORK!$F$2:$F$55=1))
This means look in the work tab in column F and find the number 1


The double brackets at the start and end of this formula have to be added and * signs between each of the ()


Hope this helps have a cool Weekend