Search code examples
vbaexcelstring-concatenation

Excel VBA Consecutive and non-consecutive numbers concatenation

I'm new to programming and have been puzzling over this challenge for a while. I suspect the solution would involve nested conditionals and/or a user defined function. I have a table similar to this which has been sorted from smallest to largest in the "sheet#" column:

QTY Type Sheet#
1    B    1000
5    B    1001
3    B    1002
2    B    1005
7    B    1009
4    B    1010
2    B    1010
3    B    1010
1    B    1011
2    B    1012
6    B    1013
8    B    1013
1    B    1015
1    B    1015
2    B    1016

There are several things to consider: Some of the sheet numbers are duplicates, some of the sheet numbers are missing (for example 1006 thru 1008 are missing from the list above)some of the sheet number are consecutive, some of them are duplicates and then consecutive with the next sheet number (as in 1010, 1010, 1010, 1011)

The result I need to produce is three cells: A total of the quantity, a translation of the letter (as in "B" stands for "Beam" and a concatenation of the sheet numbers that would read as follows

QTY  Type Sheet#
48   Beams On sheets 1000 to 1002, 1005, 1009 to 1013, 1015 and 1016

The Translation from "B" to "Beams" is only one case. I have another table on a separate worksheet that lists all the types, eg. B = Beam, C = Column, BR = Brace, PS = Pipe support, etc.

Any guidance would be appreciated very much.

Solution

  • Copy the B:C to another area (I placed in G:H)

    Use the "Remove duplicates" function under the data tab

    In F2 enter: =SUMIF(B:B,G2,A:A)

    In I2 put in a vlookup formula to pull back the meaning of B or C or whatever

    In J2 put the following formula: =IF(G2<>G1,"On sheets " & H2,IF(AND(H2=H1+1,H2=H3-1),J1,IF(H2=H1+1,J1 & " to " & H2,J1 & ", " & H2)))

    Drag F2, I2 and J2 down.

    This is how it looked when I played with some test data (Didn't do the vlookup, it doesn't have a bearing on my tests)

    QTY TypeSheet#  
48  B   1000    On sheets 1000
48  B   1001    On sheets 1000
48  B   1002    On sheets 1000 to 1002
48  B   1005    On sheets 1000 to 1002, 1005
48  B   1009    On sheets 1000 to 1002, 1005, 1009
48  B   1010    On sheets 1000 to 1002, 1005, 1009
48  B   1011    On sheets 1000 to 1002, 1005, 1009
48  B   1012    On sheets 1000 to 1002, 1005, 1009
48  B   1013    On sheets 1000 to 1002, 1005, 1009 to 1013
48  B   1015    On sheets 1000 to 1002, 1005, 1009 to 1013, 1015
48  B   1016    On sheets 1000 to 1002, 1005, 1009 to 1013, 1015 to 1016
20  C   1012    On sheets 1012
20  C   1013    On sheets 1012 to 1013
20  C   1015    On sheets 1012 to 1013, 1015
20  C   1016    On sheets 1012 to 1013, 1015 to 1016

    Once done you can copy and paste as value and simply delete all but the last row per type