How can I use Excel to reorganize information between tabs?

Question

I'm pretty familiar with Excel, but this is a little above my level to figure out on my own. I have a spreadsheet, we'll call it "Workbook A", that is used on a daily basis to provide inventory information. Workbook A has many tabs, one for each day of the month. Some of the information on Workbook A comes from "Workbook B". The problem with Workbook B is that it comes from an auto-generated system so it isn't editible, it usually has several days of information all on one tab, and there isn't a good way to sort the information by date. Also, the columns in Workbook B don't match up the way that they need to be in Workbook A, and Workbook B has a bunch of garbage rows that aren't used in Workbook A. The only way to make Workbook B work with Workbook A, at the moment, is by copying and pasting individual cells and switching back and forth between the workbooks.

I'm hoping you can help me streamline this process. I want to take the information from Workbook B and insert it into Workbook A so that it's automatically added to the correct cells in each tab according to date. I'm going to assume that it will be significantly easier to do this if Workbook B is actually copied and pasted into Workbook A as its own tab, so let's call that "Tab B". Let's say that Tab B always has the date in column G and the SKU in column K, and today is the 25th of the month so I'm going to want to copy information into a tab called "25th". Let's also say that I want the SKU from Tab B column K copied into the appropriate cell that would contain this expression in tab 25th. I want an expression that says, "Find '25' in Tab B column G; copy info from Tab B column K of that row and paste it here." The expression should then say, "Find the next '25' in Tab B column G; copy info from Tab B column K of the row where that '25' was found and paste it into this column but the next row down," and keep going until it reaches the end of the list.

I hope this makes sense and is enough information to point me the right direction. Thanks in advance for any help.

Answer 1

Here is another approach using a macro assuming I understand your question. I infer that Tab B will have a list of dates in column G, that may or may not repeat, and associated SKU's in column K.



You wish to evaluate this list (automatically created each day in Tab B), extract all SKU's in column K for dates in Column G, for this day of the month, and copy them in the next available row in the 'day of the month' tab.



This example assumes you will copy them to column A of the appropriate worksheet and the sheet containing the automatic data list is 'Tab B'. If you wish to use another column to 'copy to' and/or you tab is not named 'Tab B', modify the macro:



Change the "A" reference to the column letter you wish to 'copy to' in the daily sheet, i.e. "F", "M", etc.



Change the "A1" reference to the column reference to copy to, i.e. "F1", "M1", etc.



Change the "A:A" reference to the column reference to copy to, i.e. "F:F", "M:M", etc



Change the four 'Tab B' references to your actual data sheet name, i.e. "List", "Extract", "Data", etc.



Then, copy the macro, modified as needed, to the clipboard:



Sub Update_by_Date()

Dim i, LastRow

LastRow = Sheets("Tab B").Range("G" & Rows.Count).End(xlUp).Row

For i = 1 To Sheets.Count

If InStr(Sheets(i).Name, Day(Date)) Then

shName = Sheets(i).Name

End If

Next

For i = 1 To LastRow

If IsDate(Sheets("Tab B").Cells(i, "G").Value) Then

If Day(Sheets("Tab B").Cells(i, "G").Value) = Day(Date) Then

Sheets("Tab B").Cells(i, "K").Copy Destination:=Sheets(shName).Range("A" _

& Rows.Count).End(xlUp).Offset(1)

End If

End If

Next

Sheets(shName).Range("A1").Value = "SKU"

Sheets(shName).Columns("A:A").AutoFit

End Sub



Press ALT + F11



In the menus at the top of the VBE, select INSERT > MODULE.



Paste the macro into the editing area to the right.



Close the VBE and return to the worksheet.



Press ALT + F8



When the Macros window opens, highlight the macro and click 'Options...'



Enter a letter to be used as a keyboard shortcut, and click 'OK'.



Close the Macros window.



To run the macro, press CTRL + your shortcut letter.



If you do not wish to create a keyboard shortcut, you can just press ALT + F8, select the macro, and click 'RUN' each time.



Edit: this process will work in any version of Excel 2003, 2007, or 2010. It will also work for all sheet (tab) names containing the day, i.e. '25'. It will identify '25', '25th', 'Day 25', etc.

Answer 2

Hi Dean, I think this might be a little beyond the scope of general Excel formulae and will probably require a VBA solution. I don't know how much you know about VBA, but I could probably walk you through a solution if you're interested?

I'm thinking the best approach would be to work through the list of cells in Column G of Tab B and test whether the cell contains a date. If it does, extract the day of the month from the date in order to work out which worksheet the SKU needs to be pasted into, pick up the SKU from column K, go to the appropriate worksheet, find the next blank cell in whichever column you want the data pasted into, and then move on to the next item in column G.

To get started, make sure you've got a backup copy of your workbook A and then do the following:



1. Open Excel and go into Workbook A, then press ALT + F11 on the keyboard to open the VB Editor.

2. From the menu, choose Insert -> Module

3. Copy and paste all of the lines below from Sub to End Sub and paste them into the module you've just inserted.

4. Go back to Excel by pressing ALT + F11 again.

5. Press ALT + F8 to display a list of macros and select the one called OrganiseData, then click the Run button.



The code you need to copy is below. I've made a few assumptions about the way your file is set up as follows:



- You're working in Excel 2003, hence the row limit of 65536

- The sheet you want to copy data from is in the same workbook as your daily sheets and is called Tab B.

- The daily sheets have already been created.

- The daily sheets have names such as 1, 2, 23 etc, rather then 1st, 2nd etc.

- On the daily sheets you want to paste the SKU data into the next blank row in Column B

- This is highly unlikely to work perfectly first time! Come back to me if something goes wrong.



All these things can be tweaked later if they're not quite right, you'll just have to let me know what does and doesn't match.



Here's the code:



Sub OrganiseData()



Dim rng As Range

Dim rngNewData As Range

Dim strSKU As String

Dim strDay As String



Worksheets("Tab B").Select



Set rngNewData = Range("G1", Range("G65536").End(xlUp))



For Each rng In rngNewData



If IsDate(rng.Value) Then



strDay = Format(rng.Value,"d")

strSKU = rng.Offset(0,4).Value



Worksheets(strDay).Select

Range("B65536").End(xlUp).Offset(1,0) _

.Value = strSKU



End If



Next rng



End Sub

Answer 3

Its not a good idea to use many tabs (re: worksheets), one for different dates. There are too many switching back and forth.



Instead, see your workbook A as single database where everything goes into one sheet. With that you can sort, subtotal by dates. You can also use pivot tables to make sense of the information where you can summarize by week, months and even years.



There is no problem copy from workbook B. As it is auto-generated, the numbers fall into predictable columns which you can extract using simple macros and paste into workbook A.



You may email me a sample so I can write out some useful macros for you.

Answer 4

The various 'Lookup and Reference' formulae can help in this situation. Some particularly useful ones:

- VLOOKUP() can look at a range of values, find the value it needs from the *leftmost* column, and return the corresponding value from the same row.

- INDEX(MATCH()) does something similar, but can return values from columns left of the search column.



Something like the following should work in your case:

=VLOOKUP( 25, '[Workbook B.xls]Sheet1'!G1:K200, 5, [true/false])

The '5' tells VLOOKUP to look in the fifth column of the given range (here, G is considered first, H second, and so on). If column G is already sorted (e.g. dates from the 1st to the 31st), set the last parameter to TRUE; otherwise, use FALSE. Adjust and copy the formula as needed.

http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx



Like some other answerers have stated, using different tabs for different dates isn't quite a good idea, even if it makes more sense visually. Excel prefers rectangular arrangements of data, and is easier to manage if you can keep everything on as few sheets as possible. You might want to consider using a database (MS Access) if you have the software.

Answer 5

This could possibly be solved using VBA.



Personally, I think that a good database program would better suit your needs rather than Excel.



The organization of data on tabs representing the days of the month seems to be a very awkward way of doing things unless there is some operational requirement for it.



Do you keep using the same tabs each month?