Compare and group like numbers and info
 

I have a column with numbers some numbers are the same.
I need to total how many times a number row appears; group it; then total
another column number value and place blank rows inbetween each.
Like this...

from this:

column 1 | column2 | column 3 | column 4
-------------------------------------------
1 | 1212 | name A | 1.09
1 | 1213 | name B | 1.23
1 | 1219 | name C | 2.98
1 | 1212 | name A | 2.11
1 | 1213 | name B | 3.22
1 | 1219 | name C | 3.24
1 | 1219 | name C | 5.01
1 | 1212 | name A | 2.03

To this:

column 1 | column2 | column 3 | column 4
-------------------------------------------
| | |
3 | 1212 | name A | 5.23
| | |
2 | 1213 | name B | 4.45
| | |
3 | 1219 | name C |11.23


I think I can use the =sum() for columns 1 and 4 and record a macro to import
and sort acending.
I don't know how I can do this grouping referencing column 2 and then space
it with blank rows.

Compare and group like numbers and info
 

Before offering suggestion:
Does each number in Col 2 always have the same name in Col 3?
That is: Is 1212 always associated with name A?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"John L" wrote in message
...
I have a column with numbers some numbers are the same.
I need to total how many times a number row appears; group it; then total
another column number value and place blank rows inbetween each.
Like this...

from this:

column 1 | column2 | column 3 | column 4
-------------------------------------------
1 | 1212 | name A | 1.09
1 | 1213 | name B | 1.23
1 | 1219 | name C | 2.98
1 | 1212 | name A | 2.11
1 | 1213 | name B | 3.22
1 | 1219 | name C | 3.24
1 | 1219 | name C | 5.01
1 | 1212 | name A | 2.03

To this:

column 1 | column2 | column 3 | column 4
-------------------------------------------
| | |
3 | 1212 | name A | 5.23
| | |
2 | 1213 | name B | 4.45
| | |
3 | 1219 | name C |11.23


I think I can use the =sum() for columns 1 and 4 and record a macro to
import
and sort acending.
I don't know how I can do this grouping referencing column 2 and then
space
it with blank rows.

Compare and group like numbers and info
 

Yes; each number will always have the same name associated with it.


"Bernard Liengme" wrote:

Before offering suggestion:
Does each number in Col 2 always have the same name in Col 3?
That is: Is 1212 always associated with name A?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"John L" wrote in message
...
I have a column with numbers some numbers are the same.
I need to total how many times a number row appears; group it; then total
another column number value and place blank rows inbetween each.
Like this...

from this:

column 1 | column2 | column 3 | column 4
-------------------------------------------
1 | 1212 | name A | 1.09
1 | 1213 | name B | 1.23
1 | 1219 | name C | 2.98
1 | 1212 | name A | 2.11
1 | 1213 | name B | 3.22
1 | 1219 | name C | 3.24
1 | 1219 | name C | 5.01
1 | 1212 | name A | 2.03

To this:

column 1 | column2 | column 3 | column 4
-------------------------------------------
| | |
3 | 1212 | name A | 5.23
| | |
2 | 1213 | name B | 4.45
| | |
3 | 1219 | name C |11.23


I think I can use the =sum() for columns 1 and 4 and record a macro to
import
and sort acending.
I don't know how I can do this grouping referencing column 2 and then
space
it with blank rows.

Compare and group like numbers and info
 

Yes; each number will always have the same name associated with it.
Thank you for your time and help.
John L.

"Bernard Liengme" wrote:

Before offering suggestion:
Does each number in Col 2 always have the same name in Col 3?
That is: Is 1212 always associated with name A?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"John L" wrote in message
...
I have a column with numbers some numbers are the same.
I need to total how many times a number row appears; group it; then total
another column number value and place blank rows inbetween each.
Like this...

from this:

column 1 | column2 | column 3 | column 4
-------------------------------------------
1 | 1212 | name A | 1.09
1 | 1213 | name B | 1.23
1 | 1219 | name C | 2.98
1 | 1212 | name A | 2.11
1 | 1213 | name B | 3.22
1 | 1219 | name C | 3.24
1 | 1219 | name C | 5.01
1 | 1212 | name A | 2.03

To this:

column 1 | column2 | column 3 | column 4
-------------------------------------------
| | |
3 | 1212 | name A | 5.23
| | |
2 | 1213 | name B | 4.45
| | |
3 | 1219 | name C |11.23


I think I can use the =sum() for columns 1 and 4 and record a macro to
import
and sort acending.
I don't know how I can do this grouping referencing column 2 and then
space
it with blank rows.

Compare and group like numbers and info
 

Hi John
I will work as if you had only 8 records but the method is the same with
more
1) Copy B1:C8 to B2 of Sheet2
2) In B1 enter the text Number and in C1 the text Name (labels needed by
Filter)
3) Select B1:C8 in Sheet2; use Data|Filter|Advance Filter and specify Unique
Only
Now you have a list of the unique numbers
3) In A2 enter the formula =COUNTIF(Sheet1!$B$2:$B$9,B2) and copy down the
column. This gives the numbers 3, 2, 3 with the sample data
4) In D2 enter the formula =SUMIF(Sheet1!$B$1:$B$8,B2,Sheet1!$D$1:$D$8)
giving 5.23, 4.45.11.23
5) Save the worksheet before trying the macro below to insert blank rows
6) Copy this subroutine to a new module ( ALT+F11 to open VBA editor; Insert
Module, then paste into macro window) and run it
Sub InsertSub()
Range("A2").Select
Selection.EntireRow.Insert
For j = 1 To 7
ActiveCell.Offset(rowOffset:=2).Activate
Selection.EntireRow.Insert
Next j
End Sub

If necessary, see David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"John L" wrote in message
...
Yes; each number will always have the same name associated with it.


"Bernard Liengme" wrote:

Before offering suggestion:
Does each number in Col 2 always have the same name in Col 3?
That is: Is 1212 always associated with name A?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"John L" wrote in message
...
I have a column with numbers some numbers are the same.
I need to total how many times a number row appears; group it; then
total
another column number value and place blank rows inbetween each.
Like this...

from this:

column 1 | column2 | column 3 | column 4
-------------------------------------------
1 | 1212 | name A | 1.09
1 | 1213 | name B | 1.23
1 | 1219 | name C | 2.98
1 | 1212 | name A | 2.11
1 | 1213 | name B | 3.22
1 | 1219 | name C | 3.24
1 | 1219 | name C | 5.01
1 | 1212 | name A | 2.03

To this:

column 1 | column2 | column 3 | column 4
-------------------------------------------
| | |
3 | 1212 | name A | 5.23
| | |
2 | 1213 | name B | 4.45
| | |
3 | 1219 | name C |11.23


I think I can use the =sum() for columns 1 and 4 and record a macro to
import
and sort acending.
I don't know how I can do this grouping referencing column 2 and then
space
it with blank rows.

Compare and group like numbers and info
 

Hello Bernard,
As of now I'm using the =COUNTIF() and the =SUMIF() thank you.
When I finish this project I will show you what I did if you would like to
see it.

Thank you for your time,
John


"Bernard Liengme" wrote:

Hi John
I will work as if you had only 8 records but the method is the same with
more
1) Copy B1:C8 to B2 of Sheet2
2) In B1 enter the text Number and in C1 the text Name (labels needed by
Filter)
3) Select B1:C8 in Sheet2; use Data|Filter|Advance Filter and specify Unique
Only
Now you have a list of the unique numbers
3) In A2 enter the formula =COUNTIF(Sheet1!$B$2:$B$9,B2) and copy down the
column. This gives the numbers 3, 2, 3 with the sample data
4) In D2 enter the formula =SUMIF(Sheet1!$B$1:$B$8,B2,Sheet1!$D$1:$D$8)
giving 5.23, 4.45.11.23
5) Save the worksheet before trying the macro below to insert blank rows
6) Copy this subroutine to a new module ( ALT+F11 to open VBA editor; Insert
Module, then paste into macro window) and run it
Sub InsertSub()
Range("A2").Select
Selection.EntireRow.Insert
For j = 1 To 7
ActiveCell.Offset(rowOffset:=2).Activate
Selection.EntireRow.Insert
Next j
End Sub

If necessary, see David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"John L" wrote in message
...
Yes; each number will always have the same name associated with it.


"Bernard Liengme" wrote:

Before offering suggestion:
Does each number in Col 2 always have the same name in Col 3?
That is: Is 1212 always associated with name A?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"John L" wrote in message
...
I have a column with numbers some numbers are the same.
I need to total how many times a number row appears; group it; then
total
another column number value and place blank rows inbetween each.
Like this...

from this:

column 1 | column2 | column 3 | column 4
-------------------------------------------
1 | 1212 | name A | 1.09
1 | 1213 | name B | 1.23
1 | 1219 | name C | 2.98
1 | 1212 | name A | 2.11
1 | 1213 | name B | 3.22
1 | 1219 | name C | 3.24
1 | 1219 | name C | 5.01
1 | 1212 | name A | 2.03

To this:

column 1 | column2 | column 3 | column 4
-------------------------------------------
| | |
3 | 1212 | name A | 5.23
| | |
2 | 1213 | name B | 4.45
| | |
3 | 1219 | name C |11.23


I think I can use the =sum() for columns 1 and 4 and record a macro to
import
and sort acending.
I don't know how I can do this grouping referencing column 2 and then
space
it with blank rows.