Add Together - vba

Question

I would like help, if possible, with a code that will do the following…

Column B has 10 different variations of contents that are always 10 characters long, starting in cell B2.

I would like to make the 10 different variations of contents in Column B into headers for row 1 starting in Column C.

Next I would like the macro to look at the contents in Column A starting in A2. Then I want to use the contents in B2 to differentiate which header the contents in A2 belong under, (in the same row respectively). Now instead of putting the actual contents of A2 under the header, I want to put the number 1.

Now I want to continue this pattern for every row in Column A:B.

When duplicates occur, I want to delete the row the duplicate occurs in and increase the original occurrence by 1.

I realized that I could use a pivot table but I would like to try to use a macro instead.

Answer 1

Do you know how to use loops. My guess is yes, since you are on a programming site.

I remember seeing most of the Excel VBA tutorials using Range(a:b) when I was doing some VBA work and this wasn't elegant to use with loops.

You can instead access individuals cells using cells(x,y) which allows you to specify an x and y coordinate, so A1 becomes 1:1 and A10 is 10:1 etc.

This should help you get started. From there you need to think logically and google what you don't know I.e. do a google search for "Excel VBA range duplicates" which will give you ideas for finding duplicates in a range.

Most of what you are asking for requires simple logic or some googling. Try doing it yourself if you get stuck at specific points, then you can post those specific problems.

Answer 2

The first part is easy, the code below should do what you are after, just drop it into a standard code module:

Private Sub transposeHeadings()

    '//Use built-in filter function to get all unique entries from column B and paste to C1
    Range("B:B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("C1"),unique:=True

    '//Copy this filtered list
    Range("C2:C11").Copy

    '//Paste into row 1, using transpose to change the rows into columns
    Range("C1").PasteSpecial Paste:=xlPasteAll, Transpose:=True

    '//Delete the temporary filtered list
    Range("C2:C11").ClearContents

    '//Remove the copy bounding box
    Application.CutCopyMode = False
End Sub

Note that this uses hard-coded ranges, you could use named/dynamic ranges to make this a bit more robust.

I'm afraid however that the second part of your question has too many unknowns to make an attempt at providing some code much use, I'm not even sure I understand exactly what you are looking to do.

As Ankur suggests, have a bash yourself and come back with any specifics you are struggling with.

Answer 3

I think I understand.

If the data looks like this

BRAND   ITEM
Bic     Eraser
Bic     Pencil
Bic     Pencil
Pentel  Marker
Sharpie Marker
Sharpie Marker

The final result should look like this?

BRAND   ITEM    Marker  Eraser  Pencil
Bic     Eraser          1
Bic     Pencil                  2
Pentel  Marker  1
Sharpie Marker  2

If so, then I suggest you sort by "Brand" (key1) and by "Item" (key2). This way the duplicates will be next to each other. It would probably be easier if you made a key combining "Brand" and "Item" together and then sort, compare and count using that key.

I usually like to start at the last row first when I might have to delete rows. This way, I can use a nice for loop. Huh? Try it.