Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Oct 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Shifting Data (2003)

    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Shifting Data (2003)

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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Shifting Data (2003)

    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.)
    Attached Files Attached Files

  4. #4
    Lounger
    Join Date
    Oct 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shifting Data (2003)

    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?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shifting Data (2003)

    You might checkout my UDF called VLIndex: <post:=615,537>post 615,537</post:>

    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)),"",vlin dex($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
    Attached Files Attached Files

  6. #6
    Lounger
    Join Date
    Oct 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shifting Data (2003)

    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Shifting Data (2003)

    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •