How to transpose multiple columns of data to multiple rows 3

[barbcouilliard]

I'm trying to figure out how to get data that is in multiple rows and columns, into one row per namekey with all the associated data to that namekey on the same row. I'm attaching screen shots of what the data currently looks like and how I need to look. End result is I need to load the data into an Access table.

I'm also attaching screen shots of the Access tables. I loaded the data into one of them, but I need the data in the one table to populate into the other table.

I don't know where I need to fix this - in Excel or after I get it to Access.
thanks

 

[SkipVought]

Hi,

Many of us have company imposed internet restrictions on accessing certain data.

Please post a coherent example HERE!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[barbcouilliard]

Excel data:
Namekey Category Category Code
SmithJon Category 1 56
SmithJon Category 2 19
SmithJon Category 3 42

Needs to be in this format:

Namekey Category 1 Category 2 Category 3
SmithJon 56 19 42

 

[lionelhill]

if you wish to do this in excel, it's fairly straightforward.
(1) For the new table, make a column of Namekey without duplicates (Excel has a remove duplicates tool in the data-tab of Excel2007).
(2) For the new table, do the same for categories, but now paste-transpose so it becomes a row of column headings above the first entry of Namekey.
(3) In the original table, make a helper-column containing Namekey & Category (i.e. concatenated).
(4) In the new table, in each cell, include a formula along the lines of:
=VLOOKUP($F11&G$10, mydata, 2, FALSE)
where your new table begins in G11 for example, and mydata is a named range including the concatenated helper-column from step 3, which is immediately before the category code column.
(5) Copy this down and rightwards to fill the new table as necessary.

 

[dhookom]

From your sample data, this is a simple crosstab query in Access.

Duane
Hook'D on Access
MS Access MVP

 

[xlhelp]

It's easier if you do this on another worksheet. Assuming your data is on Sheet1. Set up your namekeys on Sheet2 in Column A.

In cell B2, type the following formula
=IF(INDIRECT(ADDRESS(COLUMN(),1,,,"Sheet1"))=$A2,INDIRECT(ADDRESS(COLUMN(),2,,,"Sheet1")),"")
and copy across columns

If you get, say, three results in row 2, then, in Cell B3 change the formula to read:
=IF(INDIRECT(ADDRESS(COLUMN()+3,1,,,"Sheet1"))=$A3,INDIRECT(ADDRESS(COLUMN()+3,2,,,"Sheet1")),"")
and copy across columns.

If you get 6 more results in row 3, then change the formula in cell B4 to
=IF(INDIRECT(ADDRESS(COLUMN()+9,1,,,"Sheet1"))=$A3,INDIRECT(ADDRESS(COLUMN()+9,2,,,"Sheet1")),"")

and so on.

It may be cumbersome, but works like charm

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.

 

[dhookom]

Why do cumbersome when a quick crosstab works based on the requirements.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Ditto to Duane!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[barbcouilliard]

If the Crosstab query in Access works like a pivot table in Excel, it will not work for this. I already tried that.

I will try the crosstab query but I appreciate the other suggestions.

 

[SkipVought]

WHY NOT? My Excel PT...
[tt]
Sum of Category Code Category
Namekey Category 1 Category 2 Category 3
SmithJon 56 19 42
[/tt]

Needs to be in this format:
[tt]
Namekey Category 1 Category 2 Category 3
SmithJon 56 19 42
[/tt]

Whats the difference?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[barbcouilliard]

because putting the Codes in the Values section of a pivot table treats them as numbers and I get 0's instead of what the code is. the codes are text. ie, some are 01, 02, 03, etc.

 

[Gruuuu]

You can tell Excel to use a specific number format in the pivot table. Somewhere in the field options.

 

[xlhelp]

Barb.

Variation on my suggestion above.

In cell B2, type the following formula
=IF(INDIRECT(ADDRESS(COLUMN(),1,,,"Sheet1"))=$A2,INDIRECT(ADDRESS(COLUMN(),3,,,"Sheet1")),"")
and copy across columns. Notice I changed 2 to a 3 since your values are located in column 3.

In Cell B3 type the following formula:
=IF(INDIRECT(ADDRESS(COLUMN()+COUNTA($B$2:$O2)-COUNTBLANK($B$2:$O2),1,,,"Sheet1"))=$A3,INDIRECT(ADDRESS(COLUMN()+COUNTA($B$2:$O2)-COUNTBLANK($B$2:$O2),3,,,"Sheet1")),"")

and copy across columns and down rows. I copied till column O in each case, hence the O in the formula.

I realize this might be much easier in Access, but once I start, it's hard for me to except defeat.



Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.

 

[barbcouilliard]

thank you xlhelp, appreciate your help very much. It's true if the cross tab query works it will be easier, but it's always good to have options.

 

[SkipVought]

Codes in the Values section of a pivot table treats them as numbers

Well if you stored your values as TEXT, this would have not been a problem!

faq68-6659.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}