Creation of Array (but open to ideas)

**alonatart** · 08-19-2011, 11:01 AM

Hi everyone,

I'm completely stumped and can't seem to move forward.

Background:
I have a worksheet that is used by multiple people to gather information from third parties. The format is something vaguely like a pivot table, but in look only. My boss is totally married to this format so it cannot change. (tab "Report" in attachment). The "report" uses dropdown lists that are fed from tab "Dropdown Listing". There is vba that allows for choosing multiple entries into each cell delimited by comma.

I then take this sheet and convert it into an import into access using a macro. (tab "Data Tables - Mult Entries"). All data is text.

Load it into access along with everyone elses updates. There are about 15 people with these sheets that are actively updating daily and we load into Access so we have a place to join all the data together.

So here is the problem:
I need to then take the output of the access table, which would contain values for every column (not just those shown in tab "Data Tables - Mult Entries". The other columns come from various sources) and make it look like tab "Report"!

I've looked at over a hundred posts and have come close but nothing seems to work well. Any ideas would be great. I'll try anything at this point!!!!

**davegugg** · 08-19-2011, 11:42 AM

I don't see a tab named Report or a tab named Data Tables - Mult Entries in your attachment. Also, do you have a sheet that shows what the data looks like once exported from Access?

**alonatart** · 08-19-2011, 01:23 PM

Sorry about that, I accidentally uploaded the wrong version. Yikes. (this is my first post ever so please forgive me). I'm attaching the correct file, and have added a sheet called "Access" to show you that format coming out of access

So sorry!! (and also very excited to have you look!)

**davegugg** · 08-19-2011, 02:04 PM

So it looks to me that columns A through P on the Access sheet are already similar to the Report page, the only main difference being the cell formatting, which is easy to change. However, it seems the Stream column will become field names and the Dependency column will fill those Stream fields. Is that correct?

**alonatart** · 08-19-2011, 02:09 PM

That is correct! And they have to be comma delimited not individual rows.

**davegugg** · 08-19-2011, 03:17 PM

See the attached file. I pasted the header columns S through AE from the Report sheet. Then I put in a formula (which you can see) to fill in the table. The only problem I have is which fields use to identify a unique entry on the Report table. It seems each line has a unique entry in at least one field.

**alonatart** · 08-19-2011, 04:31 PM

Wow, that looks great! So the unique identifier on the row is in column D. So anything related to the entry should be on one line. For example, for "P1D1R1" there are 6 rows, with columns Q being the new column headers, and R being the values I need comma delimited in each cell. Those 6 rows should collapse into 1 row with all the values in the one row, broken out by header. So in Row 2, column U you'd see A4, A8. In column X you'd see G4, G6, G4 (if there's a dup that's ok). In row Z you'd see TR. All in row 2. I hope that makes sense.

**alonatart** · 08-25-2011, 02:12 PM

Any ideas?

**davegugg** · 08-25-2011, 03:41 PM

Sorry, I seemed to have lost track of this thread. I don't have much time now, but could you put in a pivot table and put column D as the row labels, all columns after P as the column labels, then concatenate the columns into a single cell?