Vlookup using Non-unique value

[CA Rohit]

Hello,

This is the first time I am posting any question...

I have the following data, wherein I have date, Design No., etc.

DATE DNO MATCHING PARTY BATCH NO THAN MTR
1.4.14 11008/2+2+2 MAROON HP WHITE 789
1.4.14 11008/2+2+2 PURPLE HP WHITE 16 1894
1.4.14 11008/2+2+2 MUSTARD HP WHITE 16 1888
1.4.14 11008/2+2+2 GREEN HP WHITE 16 900
1.4.14 11008/2+2+2 GREEN HP WHITE 1005
1.4.14 3209/3 RED HP WHITE 8 901
1.4.14 3209/3 MAROON HP WHITE 8 850
1.4.14 3209/3 PURPLE HP WHITE 8 801
1.4.14 3209/3 MUSTARD HP WHITE 8 825
1.4.14 3209/3 GREEN HP WHITE 8 949
2.4.14 3864/1 PINK HP WHITE 12 1413
2.4.14 3864/1 PURPLE HP WHITE 12 1385
2.4.14 3864/1 MAROON HP WHITE 12 1380
2.4.14 3864/1 GOLDEN HP WHITE 12 1434
2.4.14 3864/1 GREEN HP WHITE 12 1398
2.4.14 3769/6 C RED HP WHITE 8 814
2.4.14 3769/6 C R BLUE HP WHITE 8 945
2.4.14 3769/6 C MARKANTI HP WHITE 8 926
2.4.14 3769/6 C PURPLE HP WHITE 8 902
2.4.14 3769/6 C TAMBA HP WHITE 8 1051
2.4.14 3769/6 B PINK HP WHITE 8 947
3.4.14 3769/6 B MAGENTA HP WHITE 8 922
3.4.14 3769/6 B R GREEN HP WHITE 8 937
3.4.14 3769/6 B R BLUE HP WHITE 8 935.....

As one can see there are multiple designs that are manufacture in a single day.
I want to know a formula where i can get a date wise, design wise total THAN manufactured and total MTR manufactured. The answer should look like this:

DATE DNO THAN MTR
1.4.14 11008/2+2+2 48 6476
1.4.14 3209/3 40 4326

I have also tried using pivot table, but it is not giving me the required answer (Or may be I am using it the wrong way)

I am also attaching the data sheet for the reference.

Also suggest me any alternate solutions if possible..

Thanks in Advance!!!

 

[NARAYANK991]

Hi Rohit ,

If you are willing to enter the date and the design number , then you can use the SUMIFS. See your file.

Narayan

 

[CA Rohit]

Hi Narayan,

Thanks for the quick reply.. But I think there is some confusion regarding the question..
What I want is the formula to pull the date and design value (Unique Pair) from the data sheet..
Post which I can put a sum if formula to pull the totals

 

[NARAYANK991]

Hi Rohit ,

In that case , the easiest method would be to use a helper column form a concatenated value of the date and design type , and then take a unique listing of this using the standard formula ; see your file now.

Narayan

 

[SirJB7]

Hi, CA Rohit!

Give a look at the uploaded file. It implements an alternative with a single numeric helper column.

Worksheet Data:

H2: =SI(Y(A2=A1;B2=B1);H1;H1+1) -----> in english: =IF(AND(A2=A1,B2=B1),H1,H1+1)
and copy down as required.

Worksheet 'Required Answer':

A2: =SI.ERROR(INDICE(Data!A:A;COINCIDIR(FILA()-1;Data!$H:$H;0));"") =IFERROR(INDEX(Data!A:A,MATCH(ROW()-1,Data!$H:$H,0)),"")
and copy across thru B2.

C2: =SI($A2<>"";SUMAR.SI.CONJUNTO(Data!F:F;Data!$A:$A;$A2;Data!$B:$B;$B2);"") =IF($A2<>"",SUMIFS(Data!F:F,Data!$A:$A,$A2,Data!$B:$B,$B2),"")
and copy across thru D2.

Then copy down A2:D2 as required, i.e., until the 1st blank appears (in the sample file yellow shaded area indicates rows with formulas).

Just advise if any issue.

Regards!

 

[CA Rohit]

Thank you Narayan and SirJB7.

I got the required answer.. And both of your methods are working really well..

Thanks once again!!

 

[SirJB7]

Hi, CA Rohit!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!