Summarizing a table

[Anuru]

Dear Experts,
I have attached here a document for your support;

I copy data on this table from another sheet. However, that sheet can contain the same data more than one time(may be two times, three times etc.).

In this case, I have to summarize the "same details/table" above so that it shows values in column G by adding the order quantity. In other words, the summary table should show all the details one time. For example, row 3 and 4 contains the same "style". Here, I need this to be shown in only one row, but the "order quantity"(col G) to be added together (4830+2002)=6832.

I have shown some examples manually on the summary sheet;

Thanks in advance.
Anuruddha

[sandy666]

Maybe use PivotTable

[Anuru]

Maybe use PivotTable

Hi, Thanks for the reply. I prefer a formula solution.

[sandy666]

Your choice

Have a good day

[JohnTopley]

in F2 to get unique descriptions

=IFERROR(LOOKUP(2,1/(COUNTIF($F$1:F1,Table2[Description])=0),Table2[Description]),"")

in A2

=INDEX('Data Sheet'!$A$2:$R$48,MATCH($F2,'Data Sheet'!$F$2:$F$48,0),COLUMNS($A:A))

the above can be copied across to R

THEN

in G2

=SUMIF('Data Sheet'!$F$2:$F$48,$F2,'Data Sheet'!$G$2:$G$48)

In both the above, you can use "table" references but as I never use tables (old fashioned - don't like them!) I am not sure of the syntax.

See "SUMMARY (") in the attached

[Anuru]

in F2 to get unique descriptions

=IFERROR(LOOKUP(2,1/(COUNTIF($F$1:F1,Table2[Description])=0),Table2[Description]),"")

in A2

=INDEX('Data Sheet'!$A$2:$R$48,MATCH($F2,'Data Sheet'!$F$2:$F$48,0),COLUMNS($A:A))

the above can be copied across to R

THEN

in G2

=SUMIF('Data Sheet'!$F$2:$F$48,$F2,'Data Sheet'!$G$2:$G$48)

In both the above, you can use "table" references but as I never use tables (old fashioned - don't like them!) I am not sure of the syntax.

See "SUMMARY (") in the attached

Hi John,
I was away yesterday for quite a some time. I will check and let you know the feedback.

Thanks for your hard work as always.
Regards
Anuruddha

[Anuru]

in F2 to get unique descriptions

=IFERROR(LOOKUP(2,1/(COUNTIF($F$1:F1,Table2[Description])=0),Table2[Description]),"")

Hi John,
The "unique" description is "booking reference"(column=C), not the column F(Description). Some styles are having "similar descriptions", but booking reference is different. So, how can I change the above formula for column C..?

see the attached for the error it gives;two different booking references have been added since it looks for column F description as a unique value.

[Anuru]

Also pls, is there a way to get "column I(Cut off Date)" ascending order?

[JohnTopley]

In C2

=IFERROR(LOOKUP(2,1/(COUNTIF(C$1:$C1,Table2[Booking Ref.])=0),Table2[Booking Ref.]),"")

In A2

=IFERROR(INDEX('Data Sheet'!$A$2:$R$51,MATCH($C2,'Data Sheet'!$C$2:$C$51,0),COLUMNS($A:A)),"")

Change other columns as per change for A2 (Highlighted)

To get in date order, sort "Data Sheet"

[Anuru]

In C2

=IFERROR(LOOKUP(2,1/(COUNTIF(C$1:$C1,Table2[Booking Ref.])=0),Table2[Booking Ref.]),"")

In A2

=IFERROR(INDEX('Data Sheet'!$A$2:$R$51,MATCH($C2,'Data Sheet'!$C$2:$C$51,0),COLUMNS($A:A)),"")

Change other columns as per change for A2 (Highlighted)

To get in date order, sort "Data Sheet"

Hi John,
Thanks to you, now I am a bit able to understand the logic. So, I already change your formula and got it done.

By the way, for sorting date order do I need to highlight select "Column I"?

[Anuru]

sorting also done...thanks a lot.

[Anuru]

Hi John,
Hope you are fine.
I just experienced some problem in the summary table. Data are not copied in the rows 2,3,4 except the columns C and G. Also, data are not copied in the row 2 every time, and there are "0" only in column C and G.

I have attached the original work sheet for your reference.

thanks.

[JohnTopley]

The problem occurred because of the "dynamics"of the table where "Booking Ref." had many blank rows ....why I don't like tables!!!.

I changed column c formula to

=IFERROR(LOOKUP(2,1/(COUNTIF($C$1:C1,'Data Sheet'!$C$2:$C$86)=0),'Data Sheet'!$C$2:$C$86),"")

I recommend you make column C in "Data Sheet" a dynamic range or the whole of "Data Sheet" a dynamic range.

[Anuru]

Thanks John,
by the way, do you advice me to use normal work sheet type table for this? I can do that if it solves this type of errors.

[Anuru]

[QUOTE=JohnTopley;4756315]The problem occurred because of the "dynamics"of the table where "Booking Ref." had many blank rows ....why I don't like tables!!!.

I changed column c formula to

=IFERROR(LOOKUP(2,1/(COUNTIF($C$1:C1,'Data Sheet'!$C$2:$C$86)=0),'Data Sheet'!$C$2:$C$86),"")

I recommend you make column C in "Data Sheet" a dynamic range or the whole of "Data Sheet" a dynamic range.[/QUOT

Kindly explain me how to do "dynamic range" !

[JohnTopley]

Try

FORMULAS==>DEFINE NAME

Name: Booking_Ref

Refers to: =OFFSET('Data Sheet'!$C$2,,,COUNTIF('Data Sheet'!$C$2:$C$1000,"*?"),1)

in C2

=IFERROR(LOOKUP(2,1/(COUNTIF($C$1:C1,Booking_Ref)=0),Booking_Ref),"")

in A2

=IFERROR(INDEX('Data Sheet'!$A$2:$R$1000,MATCH($C2,Booking_Ref,0),COLUMNS($A:A)),"")

change other formulae as above

in G2

=IFERROR(SUMIF(Booking_Ref,$C2,'Data Sheet'!$G$2:$G$1000),"")