Consolidate columns and sum values or any better way to do

F

FaezMH

New Member
Joined
Oct 14, 2019
Messages
39
Office Version
  1. 365
Platform
  1. Windows
Hi Guys,

Good day.

I have 3 columns(A, B & C) as shown below. I am aware that I can consolidate 2 columns instead of 3 using Data > Consolidate. My intention is to get the expected result as shown at column F, G and H. Are there any way to do it using function or VBA where I can automatically runs it.

CONSOLIDATE2.xlsm
ABCDEFGH
1colAcolBcolCcolAcolBcolC
2AKARKANSAS10AKARKANSAS122
3AKARKANSAS12VAVIRGINIA2142
4AKARKANSAS100NYNEW YORK310
5VAVIRGINIA1000EXPECTED RESULTALALABAMA69
6VAVIRGINIA304CACALIFORNIA80
7VAVIRGINIA838
8NYNEW YORK290
9NYNEW YORK20
10ALALABAMA21
11ALALABAMA48
12CACALIFORNIA10
13CACALIFORNIA10
14CACALIFORNIA30
15CACALIFORNIA30
Sheet2


Thank you.
Faez
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
How about
+Fluff 1.xlsm
ABCDEFGH
1colAcolBcolC
2AKARKANSAS10AKARKANSAS122
3AKARKANSAS12VAVIRGINIA2142
4AKARKANSAS100NYNEW YORK310
5VAVIRGINIA1000ALALABAMA69
6VAVIRGINIA304CACALIFORNIA80
7VAVIRGINIA838
8NYNEW YORK290
9NYNEW YORK20
10ALALABAMA21
11ALALABAMA48
12CACALIFORNIA10
13CACALIFORNIA10
14CACALIFORNIA30
15CACALIFORNIA30
Master
Cell Formulas
RangeFormula
F2:G6F2=UNIQUE(FILTER(A2:B100,A2:A100<>""))
H2:H6H2=SUMIFS(C:C,A:A,INDEX(F2#,,1))
Dynamic array formulas.
 
Upvote 0
Solution
New__Document (60).xlsx
ABCDEFGH
1AKARKANSAS10AKARKANSAS122
2AKARKANSAS12VAVIRGINIA2142
3AKARKANSAS100NYNEW YORK310
4VAVIRGINIA1000EXPECTED RESULTALALABAMA69
5VAVIRGINIA304CACALIFORNIA80
6VAVIRGINIA838
7NYNEW YORK290
8NYNEW YORK20
9ALALABAMA21
10ALALABAMA48
11CACALIFORNIA10
12CACALIFORNIA10
13CACALIFORNIA30
14CACALIFORNIA30
Sheet6
Cell Formulas
RangeFormula
F1:H5F1=LET(a,A1:A14,b,B1:B14,c,C1:C14,ua,UNIQUE(a),ub,UNIQUE(b),s,SUMIFS(c,a,ua,b,ub),CHOOSE({1,2,3},ua,ub,s))
Dynamic array formulas.
 
Upvote 0
Fluff said:
How about
+Fluff 1.xlsm
ABCDEFGH
1colAcolBcolC
2AKARKANSAS10AKARKANSAS122
3AKARKANSAS12VAVIRGINIA2142
4AKARKANSAS100NYNEW YORK310
5VAVIRGINIA1000ALALABAMA69
6VAVIRGINIA304CACALIFORNIA80
7VAVIRGINIA838
8NYNEW YORK290
9NYNEW YORK20
10ALALABAMA21
11ALALABAMA48
12CACALIFORNIA10
13CACALIFORNIA10
14CACALIFORNIA30
15CACALIFORNIA30
Master
Cell Formulas
RangeFormula
F2:G6F2=UNIQUE(FILTER(A2:B100,A2:A100<>""))
H2:H6H2=SUMIFS(C:C,A:A,INDEX(F2#,,1))
Dynamic array formulas.
Click to expand...
Thanks a lot, Fluff. It works beautifully!(y)
 
Upvote 0
lrobbo314 said:
New__Document (60).xlsx
ABCDEFGH
1AKARKANSAS10AKARKANSAS122
2AKARKANSAS12VAVIRGINIA2142
3AKARKANSAS100NYNEW YORK310
4VAVIRGINIA1000EXPECTED RESULTALALABAMA69
5VAVIRGINIA304CACALIFORNIA80
6VAVIRGINIA838
7NYNEW YORK290
8NYNEW YORK20
9ALALABAMA21
10ALALABAMA48
11CACALIFORNIA10
12CACALIFORNIA10
13CACALIFORNIA30
14CACALIFORNIA30
Sheet6
Cell Formulas
RangeFormula
F1:H5F1=LET(a,A1:A14,b,B1:B14,c,C1:C14,ua,UNIQUE(a),ub,UNIQUE(b),s,SUMIFS(c,a,ua,b,ub),CHOOSE({1,2,3},ua,ub,s))
Dynamic array formulas.
Click to expand...
Hi lrobbo314, Thanks. somehow I still could not get it work(i used Ctrl+Shift+Enter). The results is the same as column as col A to C.
CONSOLIDATE2.xlsm
ABCDEFGH
1AKARKANSAS10AKARKANSAS10
2AKARKANSAS12AKARKANSAS12
3AKARKANSAS100AKARKANSAS100
4VAVIRGINIA1000EXPECTED RESULTVAVIRGINIA1000
5VAVIRGINIA304VAVIRGINIA304
6VAVIRGINIA838VAVIRGINIA838
7NYNEW YORK290NYNEW YORK290
8NYNEW YORK20NYNEW YORK20
9ALALABAMA21ALALABAMA21
10ALALABAMA48ALALABAMA48
11CACALIFORNIA10CACALIFORNIA10
12CACALIFORNIA10CACALIFORNIA10
13CACALIFORNIA30CACALIFORNIA30
14CACALIFORNIA30CACALIFORNIA30
Sheet2 (2)
Cell Formulas
RangeFormula
F1:H14F1=LET(a,A1:A14,b,B1:B14,c,C1:C14,ua,UNIQUE(a),ub,UNIQUE(b),s,SUMIFS(c,a,ua,b,ub),CHOOSE({1,2,3},ua,ub,s))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Upvote 0
lrobbo314 said:
New__Document (60).xlsx
ABCDEFGH
1AKARKANSAS10AKARKANSAS122
2AKARKANSAS12VAVIRGINIA2142
3AKARKANSAS100NYNEW YORK310
4VAVIRGINIA1000EXPECTED RESULTALALABAMA69
5VAVIRGINIA304CACALIFORNIA80
6VAVIRGINIA838
7NYNEW YORK290
8NYNEW YORK20
9ALALABAMA21
10ALALABAMA48
11CACALIFORNIA10
12CACALIFORNIA10
13CACALIFORNIA30
14CACALIFORNIA30
Sheet6
Cell Formulas
RangeFormula
F1:H5F1=LET(a,A1:A14,b,B1:B14,c,C1:C14,ua,UNIQUE(a),ub,UNIQUE(b),s,SUMIFS(c,a,ua,b,ub),CHOOSE({1,2,3},ua,ub,s))
Dynamic array formulas.
Click to expand...

Hi Irobbo314, my apology. Your solution is working great too. I made a mistake earlier.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Sumifs, Index, formula only summing first value
Replies
1
Views
313
etaf
etaf
G
  • Question
Macro to consolidate multiple worksheets into Master based on cell data?
Replies
1
Views
278
bobaol
B
J
  • Question
better way than consolidating?
Replies
1
Views
408
severynm
severynm
H
  • Question
'How to' question about a Dashboard
Replies
5
Views
215
etaf
etaf
J
  • Question
Complex Move data from two spreadsheets with similar data in different columns to a specific column in an existing spreadsheet(Sheet 3)
Replies
0
Views
119
JMC57
J

Forum statistics

Threads
1,215,043
Messages
6,122,822
Members
449,096
Latest member
Erald

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