Look up duplicate value and Sum value

[Akatecho]

Dear Expert Excel,

I need to you help me to find the ways to link data from another sheet to main sheet.

This Excel file has main sheet is "Calculation". I would like to get the data as following:

1. Showing list of withpack code that material code can matching
2. Summation value of withpack each month that has value.

the information sheet is "Withpack"

this sheet has Withpack code that matching with material code then some withpack code use same material code .

Could you please help me to find the ways to get data in sheet calculation.


Lookup duplicate Value and SUM.xlsx

Best regards,

[ChemistB]

In B4 copied to the right, use this array formula

=IFERROR(INDEX('Withpack Data'!$A$2:$A$11,SMALL(IF('Withpack Data'!$B$2:$B$11=Calculation!$B$2,ROW($B$2:$B$11)-1),COLUMNS($A$1:A$1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

In B8 copied right

=SUMIF('Withpack Data'!$B$2:$B$11, $B$2,'Withpack Data'!C$2:C$11)
See attachment

[Akatecho]

Dear Chemist,

Thank you for your help me but i do not understand the formula that you use Row and Columns

=IFERROR(INDEX('Withpack Data'!$A$2:$A$11,SMALL(IF('Withpack Data'!$B$2:$B$11=Calculation!$B$2,ROW($B$2:$B$11)-1),COLUMNS($A$1:A$1))),"")

Because when I apply i can not get correctly answer.

Could you please explanation me?

[boopathiraja]

Hi, As Chemist b mentioned you have to enter the formula with CTRL+SHIFT+ENTER as it as an array formula

[Akatecho]

I already enter with CTRL+SHIFT+ENTER but get the result is not correct matching, then i would like to know the meaning of Row and Column.

However i try to send the file that apply to use. At this time i cannot attached file.

[Akatecho]

Look up Diplicate ValueV2.xlsx

this is the attached file, please help to correct at Cell C11

Thank you inadvance

[micope21]

Hi

What ChemistB done is correct what you ask for with sample file you give?

What you should have done!, is put similar sample as your own woodwork file!!
Then you would have a clear answer how it work!!

See the file! behalf chemistB

Regard

[XOR LX]

Hi,

The reason you're having issues is that:

a) You haven't amended all of the range references accordingly
b) The formula provided by Chemist B requires a manual adjustment, depending on which row that formula is entered in.

I personally prefer to use a generic version that can be placed in any row, without the need for then offsetting that row with a constant dependent on which row you choose to put the formula in.

The number of times I've seen OP's come back, complaining that they are not getting the correct results, to be met with a reply along the lines of "Oh, but if your formulas don't actually start in row such-and-such, you need to subtract such-and-such from the ROW part" is not inconsiderable. Hence the preference for a generic version which will always work.

Your (array) formula in C11 should be:

=IFERROR(INDEX(Withpack!$A$3:$A$735,SMALL(IF(Withpack!$J$3:$J$735=DemandCalculation!$C$10,ROW(Withpack!$A$3:$A$735)-MIN(ROW(Withpack!$A$3:$A$735))+1),COLUMNS($A:A))),"")

Regards

[XOR LX]

What ChemistB done is correct what you ask for with sample file you give?

You've made the same mistake. Did you manually filter to check your results?

Filter column J for 03106019: do you see either of the two results your formula gives?

This is the reason why the row number should never be determined by addition/subtraction of a constant: the -1 (after ROW($J$3:$J$735)-1) actually needs to become -2 here.

But how would the OP necessarily know that?

And why make it so that it needs to be amended? See the comments and improved version in my previous post.

Regards

[ChemistB]

Thanks Micope and XOR,
Yes, I got lazy and used -1 instead of using the generic form. :/

As an explanation to Akatecho
IF('Withpack Data'!$B$2:$B$11=Calculation!$B$2,ROW($B$2:$B$11)-1) returns an array of numbers based on the criteria that the value in B must equal what in Calculation!B2. Because it's set up as an array, it loops through the formula like this

Please Login or Register  to view this content.

Let's say there's a match in B2, B4 and B7
The resulting array would be {1,3,6} For clarity, the first formula above would become

Please Login or Register  to view this content.

The reason I subtract one is because we want the first term to = 1 as the first match and since our data starts in row 2, I put "-1" in the formula. If we started in Row 4, I would have put -3. The problem with this is that if someone inserts or removes rows, the formula won't adjust. XOR's formula does.

The COLUMNS part of the formula is used to pull, first the smallest row, then 2nd and so forth as that formula gets dragged to the right.
COLUMNS($A$1:A$1) counts how many columns are in the range A1:A1 which is 1 so the formula returns the smallest value. In our example above {1,3,7} that would be 1.
Then the formula gets dragged right and the formula becomes
COLUMNS($A$1:B$1) which has 2 columns which returns the 2nd smallest number which is 3. And so on.
I hope that helps you understand what the formula is actually doing.

[Tony Valko]

If you index the entire column then you don't have to worry about an offset correction.

Your (array) formula in C11 should be:

=IFERROR(INDEX(Withpack!$A$3:$A$735,SMALL(IF(Withpack!$J$3:$J$735=DemandCalculation!$C$10,ROW(Withpack!$A$3:$A$735)-MIN(ROW(Withpack!$A$3:$A$735))+1),COLUMNS($A:A))),"")

I assume the formula is being copied across the row...

=IFERROR(INDEX(Withpack!$A:$A,SMALL(IF(Withpack!$J$3:$J$735=DemandCalculation!$C$10,ROW(Withpack!$A$3:$A$735)),COLUMNS($C11:C11))),"")

An alternative is to use a single offset correction rather than an array of offsets by moving the correction outside of the SMALL function:

=IFERROR(INDEX(Withpack!$A$3:$A$735,SMALL(IF(Withpack!$J$3:$J$735=DemandCalculation!$C$10,ROW(Withpack!$A$3:$A$735)),COLUMNS($C11:C111))-MIN(ROW(Withpack!$A$3:$A$735))+1),"")

[XOR LX]

Not sure I understand this comment. Could you clarify?

An alternative is to use a single offset correction rather than an array of offsets by moving the correction outside of the SMALL function

I understand that it works just as well, and I believe someone like Chemist B once tried to explain that it offers a small advantage in performance, though I don't see where one of the following has a "single offset" correction versus the other having an "an array of offsets": doesn't it amount to the same thing in the end, or am I missing something?

=IFERROR(INDEX(Withpack!$A$3:$A$735,SMALL(IF(Withpack!$J$3:$J$735=DemandCalculation!$C$10,ROW(Withpack!$A$3:$A$735)),COLUMNS($A:A))-MIN(ROW(Withpack!$A$3:$A$735))+1),"")

=IFERROR(INDEX(Withpack!$A$3:$A$735,SMALL(IF(Withpack!$J$3:$J$735=DemandCalculation!$C$10,ROW(Withpack!$A$3:$A$735)-MIN(ROW(Withpack!$A$3:$A$735))+1),COLUMNS($A:A))),"")

Regards

[olwy]

Dear all, of course, I am not so experienced as you are, but why you are using such a difficult formulas? In a excel, you can use SUMIFS formula (or it this case even old sumif).
I have created 2 version of it, simpler and fast enough. In the first one, you have to add the same formula 3 times (for B4; C4; D4),
or in a second one, you can just create additional column related to what is in row 4, using simple sumif.
Why you need to use such a difficult array formulas?
Lookup duplicate Value and SUM - simplified.xlsx

[Tony Valko]

If you have:

ROW(A5:A10)-ROW(A5)+1

You calculate an array of offset corrections:

SMALL(ROW({5;6;7;8;9;10})-5+1 = {1;2;3;4;5;6},1) = 1

If you place the offset correction outside the SMALL function you only calculate a single offset:

SMALL(ROW({5;6;7;8;9;10}),1)-5+1 = 1

On a small range the difference is insignificant but on a large range you can save a few "clicks" of the calculation clock.

[XOR LX]

On a small range the difference is insignificant but on a large range you can save a few "clicks" of the calculation clock.

Many thanks for the explanation, and I see what you mean now.

Yes, quite small indeed, I would imagine! Having said that, that's probably good enough reason to start using it (or your alternative version which uses an entire column).

Cheers

[Tony Valko]

Why you need to use such a difficult array formulas?

It looks like the OP wanted to extract the data that meets a condition:

1. Showing list of withpack code that material code can matching

Like everything else in this world, the formula may seem complicated when you are not used to it and seeing it/using it for the first time.

However, once you have used it several times and when you understand how it works you'll realize that it's actually quite simple and makes perfect logical sense.

[Tony Valko]

Many thanks for the explanation, and I see what you mean now.

Yes, quite small indeed, I would imagine! Having said that, that's probably good enough reason to start using it (or your alternative version which uses an entire column).

I used to do it the same way as everyone else.

Then, one day I was doing something that used one of those types of formulas and I thought to myself: Hmmm... What happens if I do this and that?

[XOR LX]

I used to do it the same way as everyone else.

Then, one day I was doing something that used one of those types of formulas and I thought to myself: Hmmm... What happens if I do this and that?

That type of thought is to be commended. As I was saying just the other day, we shouldn't take it for granted that the tried-and-tested constructions are necessarily beyond improvement:

http://www.excelforum.com/tips-and-t...ift-enter.html

(Funnily enough, name-dropped you in that thread as well! Hope you don't mind!)

Regards

[Tony Valko]

Don't mind at all!