Create Columns Based on Subgroupings

[nguyeda]

I'm not even sure how to search the forums or google for an existing solution. What I'm trying to do is create columns based on subgrouping. See attached example in excel.

It's almost like transposing but not it.

The names of the columns don't really matter. It's to be used in a dynamic excel workbook for looksups.

An excel solution would work too!

 

[nguyeda]

In the real set there is about 77 groups and at least 10 subgroups per group

 

[namliam]

would you be OK, with creating some VBA to do this? Dont really see a "query" way to do this off the top of my head.

Should be relatively easy to convert this from rows to Columns in VBA... either in Access or in Excel... think there are even some samples to be found here to build a query combined with some VBA to do this....

 

[nguyeda]

I would be ok doing some VBA. That was just one example... in another case we made need up to 750 columns. May have to use Excel for this.

 

[namliam]

I think the best way to go about it is to have a query like the excel file you attached and loop all records while writing to a text file...

Open text file
Open query
While Not Eof
if group = prevgroup then
var = var & "," & Subgroup
else
write var to file
var = Group
endif
next line
loop
write var

If you do this to a csv, you are then able to easily open the file in excel.

Another question though, how do you determine the order of subgroups if you are intending to do lookups in Excel... Sure alphabetical... Sub groups to a, a1,a2,a3...
What will happen if a11 is inserted?

 

[nguyeda]

I found a solution on an excel forum. The subgroups could be random though and they can be sorted randomly as long as the would end up on the same row as major group "A."

Thanks for your help!