Sort large list of data from a pulldown menu and reorganize it on another sheet.

I have program I am trying to develop and am having some difficulty in creating a macro to do what I need it to do.

I have attached a simple versions so you can better understand what I need. The actual application will have many more lines of data and have a longer choice list but overall this simplified version should get me to where I need to be for understanding how to accomplish this on a larger scale.

It’s important to note that I really don’t understand the whole concept of tables so I would prefer that this be a macro as I can then understand how the process of the macro works by looking at it. I just don’t know how to write it from scratch.


So this is what it does:
On Sheet1 you will in your information in A:D. You are forced to choose from the pulldown option in B and C. This list will continue to grow so the macro will need to check to see how many times it needs to run to complete the task.

Sheet2 is where all the magic happens. You choose the person from whom you want to run the report for in B1. Again it’s a pulldown menu. Once you do that you click on the Run button and it will then take all of the data from Sheet1 (I have included it in yellow for illustration purposes only) and it will then sort the data for just that person into the specific colors as seen in J:Q. Basically it puts the data (date and price) into the correct color column.

IF you would be so kind as to notate in the code what is actually going on I SHOULD be able to alter it for my specific needs.

Thank you so very much for your assistance with this.

-Ken

Hi Sungen,

I am pretty sure there are a few excel guru's out there that can write a macro for this.

Attached is an alternative that you may find acceptable.

You can achieve a similar result with a PIVOT table with a slicer.

See attached file as example.

Hope this is helpful.

Cheers

You can do it with a few formulae, as follows:

In F10 of Sheet1 I put this formula:

=IF(B10<>Sheet2!$B$1,"-",C10&"_"&COUNTIFS(B$10:B10,B10,C$10:C10,C10))

coloured it blue, and copied it down beyond the extent of data, to accommodate more data being added.

Then I set up a data validation drop-down in cell B1 of Sheet2 and put this formula in J2:

=IFERROR(INDEX(Sheet1!$A:$A,MATCH(J$1&"_"&ROWS($1:1),Sheet1!$F:$F,0)),"")

and this one in K2:

=IFERROR(INDEX(Sheet1!$D:$D,MATCH(J$1&"_"&ROWS($1:1),Sheet1!$F:$F,0)),"")

Then this pair of formulae can be copied into L2:M2, N2:O2, and P2:Q2, and then after setting up the colours again you can copy the formulae down as far as you need.

Then you can choose a different name in B1 and the green table will change automatically.

Hope this helps.

Pete

walruseggman- this is what i am looking for..... if i might be able to burden you just a little more....... I have attached the new workbook that contains your macro.


Lets say that on Sheet1 J1 has a date in it. It will be the same date that will be used for all 4 colors on Sheet2. Lets say then J2 is $100.00, J3 is $500.00, J4 is $600, and J5 is $900.

What I would like to do it after the macro is run….
If looks at col J to see if there is anything in it. If so.. it puts the data from Sheet1 J1 and J2. Then it checks L on Sheet2 to see if there is anything there and so on. If there is not anything in the column then it does not put anything there.

Thank you so much!!!!!

Actually this is ALMOST right. I ONLY want to add the special date and special number IF there is already existing data in that line. Now it looks like its adding it only if there is NOT anything there.

Thank you!

Try

Sub test()
    Dim myName, r As Range, rng As Range
    With Sheets("sheet2")
        .Range("j1").CurrentRegion.Offset(1).ClearContents
        myName = .Range("b1").Value
        If myName = "" Then Exit Sub
        Set rng = .Range("j1", .Cells(1, Columns.Count).End(xlToLeft))
        With Sheets("sheet1").Range("a9").CurrentRegion
            .Parent.AutoFilterMode = False
            For Each r In rng
                If r.Value <> "" Then
                    .AutoFilter 2, myName
                    .AutoFilter 3, r.Value
                    If .Parent.Evaluate("subtotal(3," & .Columns(2).Address & ")") > 1 Then
                        With .Offset(1).Resize(.Rows.Count - 1)
                            Union(.Columns(1), .Columns(4)).Copy
                            r(2).PasteSpecial xlPasteValues
                        End With
                    End If
                    .AutoFilter
                End If
            Next
        End With
    End With
End Sub

YEP....... THANKS GUYS!!! this thread is CLOSED!!!