Shifting Data (2003)

Topic

New Reply

With the help of 3 of Woodys greatest, we have created a pivot table that displays all part numbers that have multiple bin locations. We will use this report for an upcoming physical inventory, printing this data and distributing to our count teams. In looking at the pivot table output, I am estimating the report I will be generating is going to be extremely long.

I am looking for a way to take data from a table (see attached) and create a single row for each specific product/part number and having the appropriate multiple bin locations on the same row.

I have attached a file with two tabs. The first tab is just the raw data the pivot table is pulling from. The second tab shows the pivot table and an additional table that I created manually to illustrate exactly what I need to create to save A TON of trees and time.

Any help would be greatly appreciated.

JG

Reply | Quote

Replies

Is there any chance you could do this in Access? It would be a lot easier there, using a crosstab query.

Reply | Quote

I do have Access 2003, but my experience is VERY limited. I have created a new db, imported data from the excel file, created a table and but am stuck on the crosstab query. All I keep getting is the list of product number and a very wide report showing data I don’t understand. Could you help with the steps on the crosstab query?

Reply | Quote

I have attached a sample database with the imported table from your workbook, and three queries.
qryProducts selects the products with more than one bin location.
qrySeqNo calculates a sequence number for each bin location (for all products).
qryCrosstab is a crosstab query based on the other two queries, with the product as row header, the sequence number as column header and the first (and only) bin found as value field.
The advantage of this approach is that the result of the crosstab query will change automatically as the source data are changed.

Reply | Quote

The attached version shows a way to use array formulas with Index and Match.
I added another calculated column to the source data, to calculate the sequence number of the bin location for a product (first bin = #1, second one = #2 etc.)

Reply | Quote

You might checkout my UDF called VLIndex: post 615,537

If you (for example):
Add the UDF to a module
Put the list of Products in G2 through G whatever (gotten from Pivot table and filtering)
Put the numbers 1,2,3 in H1:J1
In H2 you can enter the formula
=IF(ISERROR(vlindex($G2,$B$2:$B$49,1,H$1)),””,vlindex($G2,$B$2:$B$49,1,H$1))
Copy from H2 to H2:Jwhatever

You will get your table. I have attached a working example

Steve

Reply | Quote

Steve,

This works very well. Thank you for the breakdown on the formula. It is very helpful to see how it is built. Thanks a million..

John

Reply | Quote