clueless

An easier thing might be to put some example data and the results that you expect.

026-33746-118
026-36313-218
1R054000
1S154110000000
D9001101
D9001101
D9001101
D9001103
D9001104
D9001111
D9001117
D9001117
F0000200 4
F0000200 4
F0000200 4
F0003600 4
M0066100 6
M0066200
M0066200
M0066300 3
M00664LH
M00664RH
M0066500 11
M0145600 2
M0166600 2
M0166600 2
M0166600 2
M0236200
M0236200
M02369LH
M02369RH
M0411700 2
M0607500
M0983500 1
M0983500 1
M1375300 2
M1375600 2
M1375700 2
M1376300 2
M1924300
M1924300
M1926600 2
M1927300
M2002500
M2002500
M2013600
M2032800 2
M2032800 2
M3011400 2
M3011500 2
M3012200 2
M3012300 2
M3016200
M3016200
M3016800
M3016800
M3017000
M3017000
M3017200
M3017200
M3017800
M3017800
MO000000
MOR01191
MOR01203
MOR01298
MOR01312
MOR01493 2
MOR01494
MOR01742
MOR01786
MOR01786
PMB01184
PMB01254
Q6055700
Q6065300
Q6129100
Q6150500 4
Q6150600 4
Q6488300
Q6488300
Q6488300
Q6488300
Q6488300
Q6488300
Q6488300
Q6488300
Q6488300
Q6734100
Q6743400
Q6743400
Q6743500
Q6743500
Q6743600
Q6743900
Q6880300
Q6937200
Q6939100
Q6940300
Q6945300
Q6955700
Q6964600
Q7048100
Q7048100
Q7048100
Q7048100
Q7048400
Q7061800
Q7071500
Q7072300
Q7072700
Q7084500
Q7097900
Q7103800
Q7138300
Q7138300
Q7138300
R6350400
R6350500
R6350500
R6350500
R6351000
R6351000
R6387400
R6561200
R6561400
R6598300
R6651900
R6719000
R6719000
R6719000
R6719000
R6719000
R6719400
R6719700
R6720500
R6733800
R6764800 4
R6767800 1
R6785000
R6785300
R6843300
R6865900
R6913500
R6913500
R6913500
R6913500
R6913500
R6913500
R6913500
R6913500
R6953700
R6953700
R7051300
R7051300
R7052900
R7068700
R7093600
S1926DK1
S1926DK2
S2134941
S2134941
S2134942
S2134942
S2134942
S2134943
S2134943
S2134945
SP1926CL
T2133031

Above are the numbers I'm dealing with. It defaults to blank if the qty is 1. If the qty is one and repeats itself as with many instances above then is there a way to have it combine them into one entry with a qty of '?' whatever it might be?

Maybe try using the COUNTIF function:

=COUNTIF(A:A, A1)

where A:A is the range where you want to count, and A1 is the text that you want to count in A:A

I'd have to specifiy what number and cells to add together? If so that won't work for me, they'll be ever changing numbers. Somewhere in the neighborhood of 10k?
The bill of materials is never the same nor the numbers used most of the time. I was hoping there was some command that I could use to do this for me? Just to combine and add a column of numbers.

Thanks anyway.

peach said:

I'd have to specifiy what number and cells to add together? If so that won't work for me, they'll be ever changing numbers. Somewhere in the neighborhood of 10k?
The bill of materials is never the same nor the numbers used most of the time. I was hoping there was some command that I could use to do this for me? Just to combine and add a column of numbers.

Thanks anyway.

Click to expand...

Heres a macro that will do (I believe) what you want. The macro assumes the data in column A and the corresponding qty number in Column B. When you run the macro it will write the new info in Column C with Totals in column D and strips out all duplicates.

If your data and qty is all in column A then you'll need to include text parsing to get your value for qty. Let me know if you need more help

Sub MOAN()
Dim TSum(10000, 1)
[A1].Select

Do Until ActiveCell.Value = 0

For K = 1 To 10000
If TSum(K, 0) = "" Then
TSum(K, 0) = ActiveCell.Value
TSum(K, 1) = TSum(K, 1) & ActiveCell.Offset(0, 1)
Exit For
End If

If TSum(K, 0) = ActiveCell.Value Then
TSum(K, 1) = TSum(K, 1) & ActiveCell.Offset(0, 1)
Exit For
End If
Next K
ActiveCell.Offset(1, 0).Activate
Loop

[C1].Select
For L = 1 To K
ActiveCell.Value = TSum(L, 0)
ActiveCell.Offset(0, 1) = TSum(L, 1)
ActiveCell.Offset(1, 0).Activate
Next L
[A1].Select
End Sub