• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto Consolidating of Data with Formula

Ateeb Ali

Member
Dear All,
I have a example data like below;

TABLE
S.NO. COLOR ITEM VALUE
1 NAVY 123 400
2 BLUE 321 512
3 MAROON 122 610
4 NAVY 123 510
5 WHITE 321 408
6 BLACK 122 512
7 NAVY 122 403
8 WHITE 123 506
9 BLACK 122 408
10 MAROON 213 612
11 WHITE 123 616
12 NAVY 123 502
13 BLACK 321 499
14 NAVY 122 388
15 WHITE 231 477
16 MAROON 122 584

I need help to get a formula which compile the data as below;
COLOR ITEM SUM VALUE
BLACK 122 920
BLACK 321 499
BLUE 321 512
MAROON 122 1,194
MAROON 213 612
NAVY 122 791
NAVY 123 1,412
WHITE 123 1,122
WHITE 231 477
WHITE 321 408

File is attached and looking forward for help, this formula will pick all detail by itself so its not a "SUMIF" where we need to enter the color and item.

in table the color and item list may add on and all new names can be added so the same should summarized with help of formula like what pivot table do.
 

Attachments

  • Formula Sheet.xlsx
    10.8 KB · Views: 2
Can I ask what is wrong with a Pivot table?

That is exactly what they are made for

upload_2018-12-11_18-14-57.png
 
Last edited:
Dear Sir,
I actually have a different issue, I wanted to learn this formula in order to apply it to a different sheet where pivot table is not working, I have attached that file here with my detail question as below;

Please find attached file, Need VB codes of following;

1. Add Line, when user click it, its information will be pasted in following table at S.NO. 1 and user can add up to 10 lines like this, rest of formulas I have already applied.
2. Delete Line, if user want to delete any line, he just need to press delete line button and line values will be blank.
3. Save Excel File, this button should save the file in xls format without vb coding.
4. Export pdf, this button should save the file in pdf with file name as style name cell and should save in the folder where original file exist.
5. import file, from this button, user can import the pre save excel file to the same file if he want to amend the calculation basis.
6. New, once user press this, the table should go blank as well as style name.

Summary (ROW 35)
Column Quality (B36): it is consolidate value of cell range E24:F33
Column Tex (C36): it is consolidate value of cell range H24:H33
Column Color (E36): it is consolidate value of cell range I24:I33
Column Total Meter (F36): this is tricky where lots of calculation required, it will concatenate the value from "Qulity, Tex, Color" and its sum from table above
 

Attachments

  • VB Codes.xlsm
    35.2 KB · Views: 5
This is a much larger and different scoped question than the original post

I don't have time to assist you further in this post
 
Back
Top