Pratorian7
New Member
- Joined
- Oct 15, 2015
- Messages
- 1
Hi everyone,
I am pretty new to using VBA code (four weeks) and I have tried to teach myself as much as possible. There are several sites I have found but nothing that answers the specific situation I am dealing with. I have a list that I get from HR each week and the list might change each week as we add/lose employees so I know that at some point the code needs to include the "Rows.Count.End(xlUp) Step -1" so that the code can find the end of the data and count back to the top of the data which begins at cell A7 (there is information above cell A7). I would like to be able to count the unique values in column A and use that as a header for a table I have on a different sheet and then use the unique values from column B and use those as the list values on my table. This continues for the following few columns. For example, column A is the "region" (which is always the same), column B would be states, column C would be cities, and column D would be employees. The information comes from a tab called HR Data and needs to copied to a tab called ListUse and the destination is row 7 for the headers, all starting in column A. The table name is Table2 which I use for dynamic and dependent cascades in a different tab so end users can select state, city, employee to see performance metrics for that person.
Here is an example of my data on the HR Data tab: (sorry for all the ... but I wanted to make this look as close to the data as possible)
Region........State....City............Employee
Central.......NE........Omaha........Jane Doe
Central.......NE........Omaha........John Doe
Central.......NE........Omaha........Dave Doe
Central.......MO.......St. Louis......John Smith
Central.......MO.......St. Louis......Jane Smith
Central.......MO.......St. Louis......Chris Smith
Central.......MO.......Des Pres......Jane Brown
Central.......MO.......Des Pres......Dan Brown
Central.......KS........Kansas City..Joe Doe
Central.......KS........Kansas City..Jane Jones
Central.......KS........Kansas City..John Jones
Central.......KS........Wichita........Brian Johnson
Central.......KS........Wichita........Bob Brown
What I'm trying to get in my table is:
Column A.......Column B........Column C.........Column D......Column E.........Column D........Column E.........Column F........Column G
Central...........NE.................MO.................KS................Omaha............St. Louis ........Des Pres..........Kansas City.....Wichita
NE.................Omaha...........St. Louis..........Kansas City ...Jane Doe.........John Smith......Jane Brown.......Joe Doe..........Brian Johnson
MO.....................................Des Pres.........Wichita..........John Doe..........Jane Smith......Dan Brown........Jane Jones......Bob Brown
KS..............................................................................Dave Doe.........Chris Smith............................John Jones
I think looping would be time consuming since I have a few hundred employees in a little over a hundred cities.... Would a combination of formula and auto filter on unique values be better? I have no idea how to take the unique values from an auto filter and make them display in a horizontal fashion. Any help would be greatly appreciated!!!!!!!
I am pretty new to using VBA code (four weeks) and I have tried to teach myself as much as possible. There are several sites I have found but nothing that answers the specific situation I am dealing with. I have a list that I get from HR each week and the list might change each week as we add/lose employees so I know that at some point the code needs to include the "Rows.Count.End(xlUp) Step -1" so that the code can find the end of the data and count back to the top of the data which begins at cell A7 (there is information above cell A7). I would like to be able to count the unique values in column A and use that as a header for a table I have on a different sheet and then use the unique values from column B and use those as the list values on my table. This continues for the following few columns. For example, column A is the "region" (which is always the same), column B would be states, column C would be cities, and column D would be employees. The information comes from a tab called HR Data and needs to copied to a tab called ListUse and the destination is row 7 for the headers, all starting in column A. The table name is Table2 which I use for dynamic and dependent cascades in a different tab so end users can select state, city, employee to see performance metrics for that person.
Here is an example of my data on the HR Data tab: (sorry for all the ... but I wanted to make this look as close to the data as possible)
Region........State....City............Employee
Central.......NE........Omaha........Jane Doe
Central.......NE........Omaha........John Doe
Central.......NE........Omaha........Dave Doe
Central.......MO.......St. Louis......John Smith
Central.......MO.......St. Louis......Jane Smith
Central.......MO.......St. Louis......Chris Smith
Central.......MO.......Des Pres......Jane Brown
Central.......MO.......Des Pres......Dan Brown
Central.......KS........Kansas City..Joe Doe
Central.......KS........Kansas City..Jane Jones
Central.......KS........Kansas City..John Jones
Central.......KS........Wichita........Brian Johnson
Central.......KS........Wichita........Bob Brown
What I'm trying to get in my table is:
Column A.......Column B........Column C.........Column D......Column E.........Column D........Column E.........Column F........Column G
Central...........NE.................MO.................KS................Omaha............St. Louis ........Des Pres..........Kansas City.....Wichita
NE.................Omaha...........St. Louis..........Kansas City ...Jane Doe.........John Smith......Jane Brown.......Joe Doe..........Brian Johnson
MO.....................................Des Pres.........Wichita..........John Doe..........Jane Smith......Dan Brown........Jane Jones......Bob Brown
KS..............................................................................Dave Doe.........Chris Smith............................John Jones
I think looping would be time consuming since I have a few hundred employees in a little over a hundred cities.... Would a combination of formula and auto filter on unique values be better? I have no idea how to take the unique values from an auto filter and make them display in a horizontal fashion. Any help would be greatly appreciated!!!!!!!
