Switch data from columns to rows 1

[attrofy]

Here is my scenario. I have 22 locations (cols A:V), and 400 unique physical items. As it is laid out currently:

LocA    LocB    LocC    LocD
Item1   Item8   Item14  Item22
Item2   Item9   Item15  Item23
Item3   Item10  Item16  Item24
Item4   Item11  Item17  Item25

As can be seen, this is ideal for knowing which item is located in which "bin" (location). Each Column is sorted alphabetically (as each Item # has a unique name).

However, when we want to find a particular item, there is no easy way, short of reading through all 22 locations (which subject to expand and contract).
So on tab 2, I created a sequential list of all the item numbers (Col A) and their locations (Col B). To date, I have had to copy these lists manually. This isn't hard, and not time consuming when creating an entire list, but these items are subject to move locations - frequently - and the number of locations is subject to change as well. So when I make a change on tab 1, I manually have to change all the items on tab 2.

My basic question is how can I populate the list on the second tab automatically from the changes made to the first tab.

I have gotten close with some vlookups and index functions, but I am missing something basic. This shouldn't be a dificult task, but for some reason, I am not finding a simple solution. Any help is greatly appreciated.

Thanks in advance.

BTW, this information is coming from an external database, and can't be sorted before export. So, there needs to be an EASY way to move this data (i.e. copying a function from cell to cell - as opposed to something more convoluted through VBA).

 

[firefytr]

I would probably just use code instead of trying to whack your head with some formula solution. It's definitely possible, but the maintenance on it would be horrendous (IMHO). You can use ..

Private Sub Worksheet_Activate()
    Dim rngFormula As Range, c As Range, rngFind As Range
    With Sheets("Sheet1") 'adjust to your 'tab1'
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        Me.Range("A2:A" & Me.Rows.Count).ClearContents
        .Range("A2", .Cells(.Rows.Count, 1).End(xlUp)).Copy
        Me.Cells(Me.Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        .Range("B2", .Cells(.Rows.Count, 2).End(xlUp)).Copy
        Me.Cells(Me.Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        .Range("C2", .Cells(.Rows.Count, 3).End(xlUp)).Copy
        Me.Cells(Me.Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        .Range("D2", .Cells(.Rows.Count, 4).End(xlUp)).Copy
        Me.Cells(Me.Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        Set rngFormula = Me.Range("B2:B" & Me.Cells(Me.Rows.Count, 1).End(xlUp).Row)
        rngFormula.Offset(0, -1).Sort key1:=rngFormula.Offset(0, -1)(1, 1), order1:=xlAscending
        For Each c In rngFormula
            Set rngFind = .Cells.Find(c.Offset(0, -1).Value, MatchCase:=True)
            c.Value = .Cells(1, rngFind.Column).Value
            Set rngFind = Nothing
        Next c
        Application.CutCopyMode = False
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    End With
End Sub

To use:
Right click the 'tab2' tab and select View Code, paste on right. It will fire when you activate the sheet. Change any sheet names to suit.

HTH

-----------
Regards,
Zack Barresse

 

[attrofy]

Awsome - it seems to mostly be working. I can't understand what is going on with it, but it only copies data over to about column D. I don't really understand the code, but in copying the info for all the cells (through Cell V) and iterating the number after the "," up through 21, it seemed to work, but got stuck in an infinite loop. So that number after the comma must do something rather important such as the numbers of times to count, etc.

Anyway, any thoughts?

Thanks again for the code.

 

[firefytr]

Sorry, maybe change to ...

Private Sub Worksheet_Activate()
    Dim rngFormula As Range, c As Range, rngFind As Range, i As Long
    With Sheets("Sheet1")    'adjust to your 'tab1'
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        Me.Range("A2:A" & Me.Rows.Count).ClearContents
        For i = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
            .Range(.Cells(2, i), .Cells(.Rows.Count, i).End(xlUp)).Copy
            Me.Cells(Me.Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        Next i
        Set rngFormula = Me.Range("B2:B" & Me.Cells(Me.Rows.Count, 1).End(xlUp).Row)
        rngFormula.Offset(0, -1).Sort key1:=rngFormula.Offset(0, -1)(1, 1), order1:=xlAscending
        For Each c In rngFormula
            Set rngFind = .Cells.Find(c.Offset(0, -1).Value, MatchCase:=True)
            c.Value = .Cells(1, rngFind.Column).Value
            Set rngFind = Nothing
        Next c
        Application.CutCopyMode = False
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    End With
End Sub

This will copy everything up to the last row with data (according to row 1 on tab1). Let me know how it works. Also, I'm not sure what you mean about the comma thing..

-----------
Regards,
Zack Barresse

 

[attrofy]

Woot - works like a charm. I will have to sit down with the help files and try and decipher what is going on.

As for the Comma comment - I was refering to this line of code:

Me.Cells(Me.Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        .Range("B2", .Cells(.Rows.Count, 2).End(xlUp)).Copy

I copied this code 21 times, then changed the following section:

.Range("B2", .Cells(.Rows.Count, 2)

to increase the Column starting location (by 1 letter for each instance), then changed this (.Rows.Count, 2) to increase from 2-21 for each instance of the new column (i.e .Range("E2", .Cells(.Rows.Count, 5); .Range("F2", .Cells(.Rows.Count, 6) etc).
I thought this was counting the active section, but in watching the system run through its infintie loop, it was counting through each column, and I am guessing, doing it the number of times specified after the comma. But even after letting it sit for 15 minutes, it was still counting.

Anyway, thanks again for the fast response, and the great code.

 

[firefytr]

You're right in the fact that it's the column number (i.e. 5 = E, 6 = F, etc) but I'm not sure why it'd be stuck in an infinite loop, there was no loop on the first version. But anyway, I'm glad it's working for you.

-----------
Regards,
Zack Barresse