Lookup/Sum Unique Values

E

ekhawaja

Board Regular
Joined
Dec 16, 2018
Messages
98
Office Version
  1. 365
Hello,

Thanks in advance to this amazing platform!

Need help with a formula that would run across 20K rows of data.

Basically, this is what I have. In sheet 2, I need a formula in col D that checks for a match of Sheet2_ColA to Sheet1_Col D, if that's true, a match is checked for Sheet2_colB to Sheet1_ColAF, if BOTH conditions are true, we sum ONLY UNIQUE pairs of Sheet2colAL and Sheet2ColAM.

The data in sheet 1 is not necessarily in organized sequence (apple/banana/lemon/grape are in scattered sequence).

Here is the sample DATA and result populated in Sheet2_ColC.

SHEET 2
COL ACOL BCOL C
APPLE1 MON272.22
APPLE1 WK0
APPLE1 YR547.55
APPLE2 MON450
APPLE6 MON-17
APPLE-450
Banana1 MON2505.49
Banana1 WK914.88
Banana1 YR259
Grape5433
Grape1 YR4960.44
Grape2 MON
Grape6 MON518
Grape1 MON
Grape1 WK21.11
Grape1 YR4960.44
Grape871
.

SHEET 1
COL DCOL AFCOL ALCOL AM
APPLE6 MON519945.00
APPLE6 MON519945.00
APPLE6 MON519945.00
APPLE6 MON519945.00
APPLE1 YR25854545.55
APPLE1 YR25854545.55
APPLE2 MON404050.00
APPLE3 MON404050.00
APPLE4 MON404050.00
APPLE1 MON259820.00
APPLE2 MON35982450.00
Grape4875136452.00
Grape1545452419.00
APPLE2 MON35982450.00
APPLE2 MON35982450.00
APPLE1 MON25982272.22
APPLE1 MON25982272.22
APPLE1 MON25982272.22
APPLE1 MON25982272.22
BANANA1 YR59852259.00
GRAPE6 MON58953259.00
APPLE478646-450.00
Grape254136-2,456.00
GRAPE6 MON58954259.00
GRAPE1 YR2855334,521
APPLE1 MON25982272.22
Grape798657,889.00
Grape798657,889.00
APPLE2 MON519940.00
APPLE2 MON519940.00
grape79898
APPLE1 MON519940.00
APPLE6 MON51994-22.00
APPLE6 MON51994-22.00
APPLE1 YR404052.00
APPLE1 YR404052.00
APPLE1 YR404052.00
BANANA1 WK25854793.77
BANANA1 WK25854793.77
LEMON25986439.44
GRAPE1 YR25989439.44
BANANA2 MON25982439.44
BANANA2 MON25982439.44
BANANA2 MON25982439.44
BANANA1 MON25982954.99
BANANA1 MON25982954.99
BANANA25983954.99
BANANA25983954.99
BANANA25983954.99
BANANA1 MON404051,429.39
BANANA1 MON404051,429.39
BANANA1 MON404051,429.39
BANANA1 MON51992121.11
BANANA1 WK51994121.11
GRAPE1 WK51994121.11
GRAPE1 WK51994121.11
GRAPE1 WK25854-100.00
GRAPE1 WK25854-100.00
GRAPE1 WK259820.00
GRAPE1 WK259820.00
GRAPE1 WK259820.00
 
Last edited:
Sort by date Sort by votes
Hello, I am not sure how the SUM part is supposed to work so please test the following and let us know if some corrections are needed:

Excel Formula: 
=MAP(A2:A18,B2:B18,LAMBDA(x,y,SUM(UNIQUE(FILTER(Sheet1!AL2:AM62,(Sheet1!D2:D62=x)*(Sheet1!AF2:AF62=y),"")))))
 
Upvote 0
hagia_sofia said:
Hello, I am not sure how the SUM part is supposed to work so please test the following and let us know if some corrections are needed:

Excel Formula: 
=MAP(A2:A18,B2:B18,LAMBDA(x,y,SUM(UNIQUE(FILTER(Sheet1!AL2:AM62,(Sheet1!D2:D62=x)*(Sheet1!AF2:AF62=y),"")))))
Click to expand...
THANK YOU SO MUCH for the response. So I used the formula, but it did not do any sum. Basically col AL and AM have repeated values, so I don't want to sum duplicates, only if there is a unique pair in col AL and Col AM combined, I want to sum col AM unique pair values.
 
Upvote 0
Thanks for the feedback but I do not know to proceed now: could you please describe/post a picture what did it return (if "it did not do any sum")?
 
Upvote 0
How about:

Book1
ABCD
1FruitPeriodAmountFormula
2APPLE1 MON272.22272.22
3APPLE1 WK0
4APPLE1 YR547.55547.55
5APPLE2 MON450450
6APPLE6 MON-17-17
7APPLE-450
8Banana1 MON2505.492505.49
9Banana1 WK914.88914.88
10Banana1 YR259259
11Grape5433
12Grape1 YR4960.444960.44
13Grape2 MON
14Grape6 MON518518
15Grape1 MON
16Grape1 WK21.1121.11
17Grape1 YR4960.444960.44
18Grape871
19
Sheet2
Cell Formulas
RangeFormula
D2:D18D2=BYROW(A2:C18,LAMBDA(r,LET(t,UNIQUE(CHOOSECOLS(Sheet1!A2:AM1000,4,32,38,39)),f,FILTER(t,(INDEX(t,0,1)=INDEX(r,1))*(INDEX(t,0,2)=INDEX(r,2))),IFERROR(SUM(INDEX(f,0,4)),""))))
Dynamic array formulas.
 
Upvote 0
Eric W said:
How about:

Book1
ABCD
1FruitPeriodAmountFormula
2APPLE1 MON272.22272.22
3APPLE1 WK0
4APPLE1 YR547.55547.55
5APPLE2 MON450450
6APPLE6 MON-17-17
7APPLE-450
8Banana1 MON2505.492505.49
9Banana1 WK914.88914.88
10Banana1 YR259259
11Grape5433
12Grape1 YR4960.444960.44
13Grape2 MON
14Grape6 MON518518
15Grape1 MON
16Grape1 WK21.1121.11
17Grape1 YR4960.444960.44
18Grape871
19
Sheet2
Cell Formulas
RangeFormula
D2:D18D2=BYROW(A2:C18,LAMBDA(r,LET(t,UNIQUE(CHOOSECOLS(Sheet1!A2:AM1000,4,32,38,39)),f,FILTER(t,(INDEX(t,0,1)=INDEX(r,1))*(INDEX(t,0,2)=INDEX(r,2))),IFERROR(SUM(INDEX(f,0,4)),""))))
Dynamic array formulas.
Click to expand...
sheet 2 col c is the RESULT that I am looking for.....I tried the formula but did not get any result....in the formula (=BYROW(A2:C18), should the c18 be b18 instead?
 
Upvote 0
ekhawaja said:
Hello,

Thanks in advance to this amazing platform!

Need help with a formula that would run across 20K rows of data.

Basically, this is what I have. In sheet 2, I need a formula in col D that checks for a match of Sheet2_ColA to Sheet1_Col D, if that's true, a match is checked for Sheet2_colB to Sheet1_ColAF, if BOTH conditions are true, we sum ONLY UNIQUE pairs of Sheet2colAL and Sheet2ColAM.

The data in sheet 1 is not necessarily in organized sequence (apple/banana/lemon/grape are in scattered sequence).

Here is the sample DATA and result populated in Sheet2_ColC.

SHEET 2
COL ACOL BCOL C
APPLE1 MON272.22
APPLE1 WK0
APPLE1 YR547.55
APPLE2 MON450
APPLE6 MON-17
APPLE-450
Banana1 MON2505.49
Banana1 WK914.88
Banana1 YR259
Grape5433
Grape1 YR4960.44
Grape2 MON
Grape6 MON518
Grape1 MON
Grape1 WK21.11
Grape1 YR4960.44
Grape871
.

SHEET 1
COL DCOL AFCOL ALCOL AM
APPLE6 MON519945.00
APPLE6 MON519945.00
APPLE6 MON519945.00
APPLE6 MON519945.00
APPLE1 YR25854545.55
APPLE1 YR25854545.55
APPLE2 MON404050.00
APPLE3 MON404050.00
APPLE4 MON404050.00
APPLE1 MON259820.00
APPLE2 MON35982450.00
Grape4875136452.00
Grape1545452419.00
APPLE2 MON35982450.00
APPLE2 MON35982450.00
APPLE1 MON25982272.22
APPLE1 MON25982272.22
APPLE1 MON25982272.22
APPLE1 MON25982272.22
BANANA1 YR59852259.00
GRAPE6 MON58953259.00
APPLE478646-450.00
Grape254136-2,456.00
GRAPE6 MON58954259.00
GRAPE1 YR2855334,521
APPLE1 MON25982272.22
Grape798657,889.00
Grape798657,889.00
APPLE2 MON519940.00
APPLE2 MON519940.00
grape79898
APPLE1 MON519940.00
APPLE6 MON51994-22.00
APPLE6 MON51994-22.00
APPLE1 YR404052.00
APPLE1 YR404052.00
APPLE1 YR404052.00
BANANA1 WK25854793.77
BANANA1 WK25854793.77
LEMON25986439.44
GRAPE1 YR25989439.44
BANANA2 MON25982439.44
BANANA2 MON25982439.44
BANANA2 MON25982439.44
BANANA1 MON25982954.99
BANANA1 MON25982954.99
BANANA25983954.99
BANANA25983954.99
BANANA25983954.99
BANANA1 MON404051,429.39
BANANA1 MON404051,429.39
BANANA1 MON404051,429.39
BANANA1 MON51992121.11
BANANA1 WK51994121.11
GRAPE1 WK51994121.11
GRAPE1 WK51994121.11
GRAPE1 WK25854-100.00
GRAPE1 WK25854-100.00
GRAPE1 WK259820.00
GRAPE1 WK259820.00
GRAPE1 WK259820.00
Click to expand...
@Eric W hagi @hagia_sofia .....sorry I have a typo in the post, SHEET 2 COL C contains the result that I am looking for (I need a formula here).....sorry for the typo....thank you so much!
 
Upvote 0
ekhawaja said:
sheet 2 col c is the RESULT that I am looking for.....I tried the formula but did not get any result....in the formula (=BYROW(A2:C18), should the c18 be b18 instead?
Click to expand...
Yes, that's correct. I didn't realize at first that C was the expected answers. Incidentally, it occurred to me that if you use this formula on 20K rows of data, it might pretty inefficient since it'll have to do the UNIQUE(CHOOSE(FILTER of Sheet1 for every row of Sheet2. So I came up with Formula2:

Book1
ABCDE
1FruitPeriodExpectedFormulaFormula2
2APPLE1 MON272.22272.22272.22
3APPLE1 WK00
4APPLE1 YR547.55547.55547.55
5APPLE2 MON450450450
6APPLE6 MON-17-17-17
7APPLE-4500
8Banana1 MON2505.492505.492505.49
9Banana1 WK914.88914.88914.88
10Banana1 YR259259259
11Grape54330
12Grape1 YR4960.444960.444960.44
13Grape2 MON0
14Grape6 MON518518518
15Grape1 MON0
16Grape1 WK21.1121.1121.11
17Grape1 YR4960.444960.444960.44
18Grape8710
19
Sheet2
Cell Formulas
RangeFormula
D2:D18D2=BYROW(A2:B18,LAMBDA(r,LET(t,UNIQUE(CHOOSECOLS(Sheet1!A2:AM1000,4,32,38,39)),f,FILTER(t,(INDEX(t,0,1)=INDEX(r,1))*(INDEX(t,0,2)=INDEX(r,2))),IFERROR(SUM(INDEX(f,0,4)),""))))
E2:E18E2=LET(t,TRANSPOSE(UNIQUE(CHOOSECOLS(Sheet1!A2:AM1000,4,32,38,39))),MMULT(IF((A2:A18=INDEX(t,1,0))*(B2:B18=INDEX(t,2,0)),INDEX(t,4,0),0),SEQUENCE(COLUMNS(t),,,0)))
Dynamic array formulas.


But the Matrix Multiply can be computation intensive too, so I tried both formulas on 25,000 rows of data. Surprisingly, they both worked fine, with barely any lag at all.

Anyway, glad we could help!
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

ExcelNewbie2020
  • Solved
Extract unique values and sum duplicates
Replies
3
Views
321
alansidman
alansidman
K
  • Question Question
adjusting code or alternative on userform to change way of price average calculation
Replies
0
Views
71
KalilMe
K
S
  • Question Question
How to sum and rank with multi criteria uniquely without duplicate? For excel 2019
Replies
3
Views
594
Sochen
S
willow1985
  • Solved
Labeling a Unique/Distinct value not including any duplicates then pulling a list of those values
2
Replies
11
Views
779
Fluff
Fluff
K
  • Solved
concatenate the values and summing with comma
Replies
9
Views
396
Marcelo Branco
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top