Help Extracting Data- Probably VBA for looping?

If you upload a workbook with the sheets what you have and what you want as a result will help.
Picture doesn't help usually.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks- this is my first post here so I appreciate the advice.

Thank you. Each name and card number have merged cells, and the access levels vary in rowsize (anywhere from 1 to 4 access levels). How do I tell it to look up the name, and then copy the rows next to it to paste into columns on new worksheet, and then move to the next name? Can you tell me what I'm putting as the ranges? It would be so much easier if I could send the actual spreadsheet...lol.

or something like this with PowerQuery

Try

Sub test()
    Dim a, i As Long, ii As Long, n As Long, t As Long, w
    a = Sheets("raw data").Cells(1).CurrentRegion.Value
    ReDim b(1 To UBound(a, 1), 1 To 3): n = 1
    b(n, 1) = a(1, 1): b(n, 2) = a(1, 2)
    For i = 2 To UBound(a, 1)
        If a(i, 1) <> "" Then
            n = n + 1: t = 2
            b(n, 1) = Array(a(i, 1), a(i, 3)): b(n, 2) = a(i, 2)
            ii = 1
            Do While a(i + ii, 1) = ""
                t = t + 1
                If UBound(b, 2) < t Then ReDim Preserve b(1 To UBound(b, 1), 1 To t)
                b(n, t) = a(i + ii, 2): ii = ii + 1
                If i + ii > UBound(a, 1) Then Exit Do
            Loop
            i = i + ii - 1
        End If
    Next
    ReDim Preserve b(1 To UBound(b, 1), 1 To UBound(b, 2) + 1)
    b(1, UBound(b, 2)) = a(1, UBound(a, 2))
    For i = 2 To n
        w = b(i, 1)
        b(i, UBound(b, 2)) = b(i, 1)(1): b(i, 1) = b(i, 1)(0)
    Next
    With Sheets("desired output").Cells(1).Resize(n, UBound(b, 2))
        .Value = b
        .Columns.AutoFit: .Parent.Activate
    End With
End Sub

To whom you are talking?

Sandy666, how did you get the merged name fields and accesses lined up in that power query?

Thanks bunches. For some reason, I'm not seeing Power Query in my Data tab- I have the regular query, but my build of 2016 must be different. There are about 4,000+ lines of data, and it looks like you manually created each of the merged columns? I already transposed the table, it was just getting it into that final format. It would be quicker to just cut and paste than create a query for each name?

flexchick

Have you tried my attachment in #8?

Thank you jindon- that one does what I need. Aside from making sure the sheet names are the same in .xlsm coding, what else will I need to change to get it to work on my larger dataset?

Hi Sandy- I have 2016- and it shows as "Get and Transform" in my version. Thanks for all of the helpful information!

Originally Posted by flexchick

Thank you jindon- that one does what I need. Aside from making sure the sheet names are the same in .xlsm coding, what else will I need to change to get it to work on my larger dataset?

It should work in any size of the data.
If you have problem, I need to see your workbook.

You are welcome

Why MS changed this name - I don't know
but IMHO PowerQuery sounds much better than Get&Transform, eg. use Power (Query) Luke !

Thanks Jindon- can I private message you with this?

PM sent...............

Thanks- reply sent. Let me know what you think!

OK, I see
Just replace

    a = Sheets("raw data").Cells(1).CurrentRegion.Value

with

    With Sheets("raw data")
        a = Intersect(.Columns("a:c"), .UsedRange).Value
    End With

And see how it goes.

Jindon, it worked beautifully. Thanks again. That was so very helpful!!

Thank you also to Sandy, and anyone else replying. This is a wonderful resource.

You are welcome and thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.