1

I have a single worksheet with 9 sheets. 1st sheet has a list of all users and data matching users. For each day they worked there will be a new row with their name (Max of 6 Rows). So A1-A3 is one name and A4-A10 is another and so on with no constant amount of days worked. Sheet 2 has a list of employees matched to their group. So A1-A5 is Sally Sam Dave Bob and Rick B1-B5 is filled with Lansing. Sheet 3-9 is each group. How would I create a macro that Grabs the Group Name from sheet 2 → Pulls a name → Grabs all data from Sheet 1 that matches the Name → Copy all data relating that name to the appropriate worksheet. And repeat for each name in sheet 2 attached to the group. Then The next group will dido to sheet 4 and so on. I know this is possible but I am new at excel and only know the basic stuff. I am able to find code that copys and pastes and VLookup might work but I am not experienced enough to mash it together.

4 Spice ups
2

It would be a lot of help if you could post some examples of what your data looks like currently. Doesn’t have to be your actual data, but just an approximation because I’m kind of having trouble wrapping my head around where things are in your sheets.

2 Spice ups
3

agree with Christopher on this one… it definitely sounds easy enough, but I need to see your sample - feel free to use “userX” and “dataX” throughout.

4

Please combine those pictures into this post, and we can remove the other one. You’ll get much more help here than in the Newbies group.

5

Ah, here is where I recommend leveraging tiny bites to solve a big problem. Let’s do some functionalizing in Excel, since that’s the best tool ever for abstracting blocks of executable code into a monolithic program .

Pick a sheet and an unused cell on it. Create a formula to lookup the value you’re seeking, just a “=(named range)” or whatever. Now pick another cell, and create a “=DLookup()” using the first formula as the detail to search. Then use the output of that as input to another formula, et cetera, ad nauseum. Ultimately, “mashing it together” like you say will entail taking the text of one formula and placing it back into the other, properly delimited of course, and that will give you your One Formula To Rule Them All. This is “fun”, right?

Happy to help with specifics, or feel free to PM me the sheet; if I can get it done for you in 15 minutes, I will.

Oh, wait, looking at your other post… you might just want to do this up in VBA, “properly”. Since you might have to do things like create new sheets on the fly, and you’re creating a dynamic list of names for which to query in the first place. Is Excel really the only tool available for this? It seems like a tiny, tiny bit of SQL would serve you very well, even in Access if nothing else.

6

Here is a brief example of the data that I am working with. The whole point is so that each department is able to look at their own data for cross checking to make sure it is correct.

Example.xlsx (10.7 KB)

7

Thank you for the feed back. At the moment We export this from a program we use as a CSV. I know less about SQL and VBA then I do excel. I am open to any and all ways to do this. if I do use SQL I need to be able to allow for end users to open this and edit. We will not need to create sheets on the fly It is the main page with everyone’s name and the department sheet that will be edited. It needs to be dynamic due to people coming and going.

8

I messed up when typing up the example page. There will be no blank spaces under the Name column. Bob will just repeat 5 times to fill in the gap.That is why I was thinking a VLOOKUP might work?

9

This is what I would do - short and sweet.

- Insert 2 new columns (B and C) after Name but before first Date column

- Type the first person's name from A2 into B2

- Paste this formula into B3, and drag to bottom of worksheet

	=IF(ISBLANK(A3),B2,A3)

- Paste this formula in C2 and drag to the bottom

	=VLOOKUP(B2,Grouping!$A$1:$B$7,2,FALSE)

- Create 3 copies of the DATA sheet and name them Sales, Accounting, and IT

- Use filters on each new sheet to show by department

* You will have to edit the VLOOKUP formula for your real sheet - change the seven in 
$B$7 to the number of rows you have in your Grouping sheet before you drag it to the bottom

Example_update.xlsx (16.7 KB)

10

Here is the code that I found to work for me. Thanks everyone for the help!

Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 1
Set ws = Sheets(“All users”)
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = “A1:G1”
titlerow = ws.Range(“A1:G1”).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = “Unique”
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> “” And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & “”
If Not Evaluate(“=ISREF('” & myarr(i) & “'!A1)”) Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & “”
Else
Sheets(myarr(i) & “”).Move after:=Worksheets(Worksheets.Count)
End If
ws.Range(“A” & titlerow & “:A” & lr).EntireRow.Copy Sheets(myarr(i) & “”).Range(“A1”)
Sheets(myarr(i) & “”).Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub