CODEWITHSUNDEEP
excelexcel-formulavba
rewallac
rewallac

Reputation: 9

Excel Creating a List from Beginning and End number AND tags

I am trying to create a list from an index of grouped values.
This is very similar to this, however my groups also have "tags" on then that complicate the listings.

Here is an example of my INDEX tab:

  | A  | B  | C  |  D  |
-------------------------
1 | 1  | 1  | 1  | CV  | 
2 | 1  | 2  | 2  | IS  |
3 | 1  | 3  | 3  | IS  |
4 | 2  | 4  | 5  | GN  |
5 | 2  | 6  | 7  | PS  |
6 | 4  | 8  | 11 | SQ  |
7 | 2  | 12 | 13 | SS  |
8 | 1  | 14 | 14 | AT  |
9 | 15 | 15 | 29 | AT  |
10| 4  | 30 | 33 | TYP |

Where A is the number of pages, B is the first page, C is the last page and D is the tag. I would also like to add columns such that I can keep a running tally of the tags.

  | A  | B  | C  |  D  |  E   |  F   |
---------------------------------------
1 | 1  | 1  | 1  | CV  | CV1  | CV1  |  
2 | 1  | 2  | 2  | IS  | IS1  | IS1  |
3 | 1  | 3  | 3  | IS  | IS2  | IS2  |
4 | 2  | 4  | 5  | GN  | GN1  | GN2  |
5 | 2  | 6  | 7  | PS  | PS1  | PS2  |
6 | 4  | 8  | 11 | SQ  | SQ1  | SQ4  |
7 | 2  | 12 | 13 | SS  | SS1  | SS2  |
8 | 1  | 14 | 14 | AT  | AT1  | AT1  |
9 | 15 | 15 | 29 | AT  | AT2  | AT16 |
10| 4  | 30 | 33 | TYP | TYP1 | TYP4 |

Note that the tag could occur multiple times and it may not be in sequential rows.

Here is what I want this to look like for my LIST tab:

  | A   |
---------
1 | CV1 |
2 | IS1 |
3 | IS2 |
4 | GN1 |
5 | GN2 |
6 | PS1 |
7 | PS2 |
8 | SQ1 |
9 | SQ2 |
10| SQ3 |
11| SQ4 |
and so on...

How do I add the additional columns to the INDEX tab via formulas?
How do I create the LIST via formulas? (...is this even possible?)

Upvotes: 0

Views: 1993

Answers (2)

Jeff Mercado
Jeff Mercado

Reputation: 134981

The formulas should be pretty simple to write. Just consider what you're trying to accomplish.

Your first formula (in column E) is just taking a running count of the tags (in column D). So you want to count all cells from the first tag up to the corresponding tag where the tag names are the same. That count is to be appended to the tag name.

=$D1 & COUNTIF($D$1:$D1, $D1)

The second formula (in column F) is just taking a running sum of the page counts (in column A). So you want to take the sum of all corresponding page counts from the first tag up to the corresponding tag where the tag names are the same. The sum is to be appended to the tag name.

=$D1 & SUMIF($D$1:$D1, $D1, $A$1:$A1)

Note that the column doesn't change nor does the starting rows of the ranges (hence the need to use absolute ranges). The only thing that changes are the rows of the tag and the row of the end range.

I don't think it would be possible to generate that list through simple formulas. As far as I know, formulas need to have a 1-to-1 correspondence with another range. A single range can yield multiple values so a formula just won't cut it. You'll need to write a VBA script to generate that.

Sub GenerateList()

    Dim usedRange As Range
    Dim count As Dictionary

    Set usedRange = Worksheets("Index").usedRange
    Set count = CountValues(usedRange)

    Dim output As Range
    Dim row As Integer
    Dim key As Variant

    Set output = Worksheets("List").Columns("A").Rows
    output.ClearContents
    row = 1
    For Each key In count.Keys()
        Dim i As Integer

        For i = 1 To count(key)
            output(row) = key & i
            row = row + 1
        Next i
    Next key

End Sub

Function CountValues( _
        usedRange As Range, _
        Optional tagsColumn As String = "D", _
        Optional valuesColumn As String = "A") As Dictionary

    Dim tags As Range
    Dim values As Range

    Set tags = usedRange.Columns(tagsColumn).Rows
    Set values = usedRange.Columns(valuesColumn).Rows

    Dim map As New Dictionary
    Dim tag As Range
    For Each tag In tags
        map(tag.Value) = map(tag.Value) + values(tag.row)
    Next tag

    Set CountValues = map

End Function

This uses a Dictionary so you'll have to reference the scripting runtime.

Upvotes: 1

ExcelVbaIsFun
ExcelVbaIsFun

Reputation: 61

It sounds like you're just trying to get a list of "Unique Values" on a separate sheet that you can use as your list. Try these pages, there are multiple VBA methods to paste unique items in a range.

Also, Advanced Filter has an option to paste unique values to another location. So none of your repeat tags would appear in this list, only unique ones for your "LIST" tab.

Anyway, not sure if that's what you're wanting, but the question was a smidge vague.

Links here:
Create Unique list
Create Unique list 2

Upvotes: 0

Related Questions