Baker's Dozen - Lookup issues

IvanYerk

New Member
Joined
Aug 26, 2018
Messages
41
I have data from a baker that comes in like this:
1582050707799.png


And I need to get this output which I have no idea how to accomplish. An array maybe?? Thanks in advance for your help!!

1582050764318.png
 

Attachments

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

bbotzong

New Member
Joined
Dec 17, 2003
Messages
22
Office Version
365, 2019, 2016, 2013, 2010
Platform
Windows
If you're willing to do a little prep to the input sheet, you can do it with either SUMIFS() or SUMPRODUCT(). The prep would involve this... put the product number on each row where there is an order (to the right of the quantity column). Secondly, if you use the SUMPRODUCT() solution, remove the pseudo underlines (---------). here's what I get:

Bakers Dozen.xlsx
ABCDEFGHIJKLMN
1Customer #Customer NameProduct and Qty
2CLUB ROLLSUNIT: DZS460SUMIFS()SUMPRODUCT()
3460530630460530630
4821Store A3460Store A 3 14 78 Store A 3 14 78
5838Store B5460Store B 5 - 91 Store B 5 - 91
6Store C - 35 91 Store C - 35 91
78Store D - - 8 Store D - - 8
8Store E - - 226 Store E - - 226
9ITALIAN SUBSUNIT: DZ530Store F - - 4 Store F - - 4
10Store G - - 66 Store G - - 66
11814Store C35530
12821Store A14530
13
1449
15
16NO POINT TORPEDOUNIT: DZ630
17
18804Store D8630
19814Store C91630
20821Store A78630
2188Store B91630
22856Store E226630
23858Store F4630
24866Store G66630
25
26564
Sheet1
Cell Formulas
RangeFormula
G4:I10G4=SUMIFS($C$4:$C$30,$B$4:$B$30,$F4,$D$4:$D$30,G$3)
L4:N10L4=SUMPRODUCT(($K4=$B$4:$B$30)*($D$4:$D$30=L$3)*($C$4:$C$30))
C7,C14C7=C4+C5
C26C26=SUM(C18:C24)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,205
Office Version
2007
Platform
Windows
Here another option with an array formula for you to consider:

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Dante Amor.xlsm
ABCDEFGH
1Customer #NameProduct460530630
2CLUB ROLLSUNIT: DZS460Store A31478
3Store B591
4821Store A3Store C3591
5838Store B5
6
7ITALLIAN SUBSUNIT: DZS530
8
9814Store C35
10821Store A14
11
12NO POINTUNIT: DZS630
13
14804Store D8
15814Store C91
16821Store A78
17838Store B91
18856Store E226
19858Store F4
20866Store G66
Sheet4
Cell Formulas
RangeFormula
F2:H4F2=IFERROR(VLOOKUP($E2,INDEX($B:$B,MATCH("UNIT*|"&F$1,LEFT($B$2:$B$200,4)&"|"&$C$2:$C$200,0)+1):INDEX($C:$C,IFERROR(MATCH("UNIT*|"&G$1,LEFT($B$2:$B$200,4)&"|"&$C$2:$C$200,0)-1,200)),2,0),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

IvanYerk

New Member
Joined
Aug 26, 2018
Messages
41
Terrific - arrays are my kryptonite! how would you modify the formula if using the customer # instead

1582058836742.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,205
Office Version
2007
Platform
Windows
Try this:

Dante Amor.xlsm
ABCDEFGH
1Customer #NameProduct460530630
2CLUB ROLLSUNIT: DZS46082131478
3838591
4821Store A38143591
5838Store B5
6
7ITALLIAN SUBSUNIT: DZS530
8
9814Store C35
10821Store A14
11
12NO POINTUNIT: DZS630
13
14804Store D8
15814Store C91
16821Store A78
17838Store B91
18856Store E226
19858Store F4
20866Store G66
Sheet4
Cell Formulas
RangeFormula
F2:H4F2=IFERROR(VLOOKUP($E2,INDEX($A:$A,MATCH("UNIT*|"&F$1,LEFT($B$2:$B$200,4)&"|"&$C$2:$C$200,0)+1):INDEX($C:$C,IFERROR(MATCH("UNIT*|"&G$1,LEFT($B$2:$B$200,4)&"|"&$C$2:$C$200,0)-1,200)),3,0),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

IvanYerk

New Member
Joined
Aug 26, 2018
Messages
41
This is awesome, works perfectly and it's GREAT karma you both are putting out in the universe being so helpful on here. Many, many, many, thanks for your time!

So it pains me throw one more wrinkle in the mix - the file from the baker comes in with the category titled cells merged (I.e. A2/B2, A7/B7, A12/B12), so when the formula has the "UNIT*| part, I have to unmerge those cells and type UNIT in the B2, B7 and B12 cells to get it to work right.

I tried all night to try and fix it myself but couldn't quite get it right.

1582116946491.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,205
Office Version
2007
Platform
Windows
It is a headache the merged cells, if you can work without it, you would be working better.

But, try this:

=IFERROR(VLOOKUP($E2,INDEX($A:$A,MATCH("*UNIT*|"&F$1,$A$2:$A$200&"|"&$C$2:$C$200,0)+1):INDEX($C:$C,IFERROR(MATCH("*UNIT*|"&G$1,$A$2:$A$200&"|"&$C$2:$C$200,0)-1,200)),3,0),"")
 

IvanYerk

New Member
Joined
Aug 26, 2018
Messages
41
Agree, but formula appears to be working - again, a thousand thanks - love learning new ways to use Excel!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,205
Office Version
2007
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,089,637
Messages
5,409,467
Members
403,265
Latest member
HMR120

This Week's Hot Topics

Top