Formula to Merge identical text in different Cells in the same Column and give the Total Count for the Merged Text

[DataQuestioner]

	A	B	C	D
	ORIGINAL TEXT	ORIGINAL COUNT	ALPHABETICAL RESULT​	NEW COUNT
1	TEST	2	TEST	4
2	TESTER	3	TESTED	5
3	TESTING	1	TESTER	6
4	TEST	1	TESTING	3
5	TESTED	4
6	TESTED	1
7	TEST	1
8	TESTING	2
9	TESTER	3
10
11	COUNT =	18	COUNT =	18

<tbody>
</tbody>

Using the sample table above, how do I get the text in Column A1:A9, with its associated counts in Column B1:B9, to be merged into unique occurrences of each word, and listed in alphabetical order in Column C, with their new merged count listed in Column D?

NOTE: Obviously, as a cross check, the Total Count in Cells B11 and D11 must be identical.

Thanks.

 

[shyy]

Use SUMIF

Excel 2010
	A	B	C	D
1	ORIGINAL	ORIGINAL	ALPHABETICAL	NEW
2	TEXT	COUNT	RESULT	COUNT
3	TEST	2	TEST	4
4	TESTER	3	TESTED	5
5	TESTING	1	TESTER	6
6	TEST	1	TESTING	3
7	TESTED	4
8	TESTED	1
9	TEST	1
10	TESTING	2
11	TESTER	3
12
13	COUNT =	18	COUNT =	18
Sheet5

Cell Formulas
Range	Formula
D3	=SUMIF($A$3:$A$11,$C3,$B$3:$B$11)
D4	=SUMIF($A$3:$A$11,$C4,$B$3:$B$11)
D5	=SUMIF($A$3:$A$11,$C5,$B$3:$B$11)
D6	=SUMIF($A$3:$A$11,$C6,$B$3:$B$11)
D13	=SUM(D3:D12)
B13	=SUM(B3:B12)

 

[DataQuestioner]

Shyy,
Thanks for your D3 to D6 formulas.

I can understand how the results in cells D3 to D6 are generated.
How does your formula arrange cells C3 to C6 in alphabetical order? I cant see that.

Remember, the formula has to create the contents for BOTH the 'C' and 'D' Columns.

 

[shyy]

Didn't catch that part, I believe you will have to use a macro. You can use the macro recorder and record yourself alphabetizing the cell C2. But inset a blank column between B and C or your will also sort Columns A and B.

For Column C use this instead since you wanted formulas.

Excel 2010

	A	B	C	D
1	TEXT	COUNT	RESULT	COUNT
2	TEST	2	TEST	4
3	TESTER	3	TESTER	6
4	TESTING	1	TESTING	3
5	TEST	1	TESTED	5
6	TESTED	4
7	TESTED	1
8	TEST	1
9	TESTING	2
10	TESTER	3
11
12	COUNT =	18	COUNT =	18

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C2 =IFERROR(INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($C$1:$C1,$A$2:$A$10),,),0)),"") D2 =SUMIF($A$2:$A$10,$C2,$B$2:$B$10) C3 =IFERROR(INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($C$1:$C2,$A$2:$A$10),,),0)),"") D3 =SUMIF($A$2:$A$10,$C3,$B$2:$B$10) C4 =IFERROR(INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($C$1:$C3,$A$2:$A$10),,),0)),"") D4 =SUMIF($A$2:$A$10,$C4,$B$2:$B$10) C5 =IFERROR(INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($C$1:$C4,$A$2:$A$10),,),0)),"") D5 =SUMIF($A$2:$A$10,$C5,$B$2:$B$10) C6 =IFERROR(INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($C$1:$C5,$A$2:$A$10),,),0)),"") C7 =IFERROR(INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($C$1:$C6,$A$2:$A$10),,),0)),"") C8 =IFERROR(INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($C$1:$C7,$A$2:$A$10),,),0)),"") C9 =IFERROR(INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($C$1:$C8,$A$2:$A$10),,),0)),"") C10 =IFERROR(INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($C$1:$C9,$A$2:$A$10),,),0)),"") B12 =SUM(B2:B11) D12 =SUM(D2:D11) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[DataQuestioner]

Shyy,
Thanks for your latest code.

If my 'A' + 'B' Columns have 1000's of cells with single word text and numerals, respectively, I'm not sure that I would want to insert the "=IFERROR..." function that many times - is there not an Array Formula solution for this problem?

 

[shyy]

Not sure what you mean,

add in the IFERROR only because cells return #N/A. Whether its a single word or a number, it still works. Unless you have another example to illustrate what you mean.

Shyy,
Thanks for your latest code.

If my 'A' + 'B' Columns have 1000's of cells with single word text and numerals, respectively, I'm not sure that I would want to insert the "=IFERROR..." function that many times - is there not an Array Formula solution for this problem?

 

[DataQuestioner]

Your formulae in post #4 require me to add the "IFERROR..." to each 'D' Column cell - Yes?

If I have 1000's of cells to be completed, based on 1000's of text+number counts in 'A' + 'B' Columns, then am I supposed to add your "IFERROR..." formula to each and every 'D' Column cell 1000's of times? That's too time consuming!

 

[shyy]

D2=SUMIF($A$2:$A$10,$C2,$B$2:$B$10)

Where is the IFERROR?

 

[DataQuestioner]

Sorry, I meant to say do you want me to add the "SUMIF..." formula to every one of the 1000's of 'D' Column cells?

 

[shyy]

Yes you have to SUM up all the numbers in column C.

Don't know if this will make any difference when it comes to calculation.

=IF(ISBLANK(C2),"",SUMIF($A$2:$A$10,$C2,$B$2:$B$10)