Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Not sure what to use? Lookup / Index / Match etc
Each week I get a huge amount of data sent to me in the format below
12500 12500 12500 13100 13100 13100 User Number JAN FEB MAR JAN FEB MAR Month Name Beans 5 8 1 6 3 2 Bread 11 7 2 4 2 0 Jam 7 3 0 4 1 0 Total 22 18 3 14 6 2 So from this table I can see that USER 12500 sold 5 Beans in Jan, 8 in Feb etc I want to present the data with the user on the left, the products along side it, like : Jan Jan Jan Feb Feb Feb Beans Bread Jam Beans Bread Jam 12500 5 11 7 8 7 3 13100 6 4 4 etc At first I though I could transpose the data when pasting but that isnt working. I am scratching my head as I dont know if I would be best using a lookup, and index, IF's? Any advice on the best way to tackle it? Many thanks! Carl. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Not sure what to use? Lookup / Index / Match etc
First, this may not be the way to go.
But if your data were laid out nicer--no gaps in column A--like: User Number 12500 12500 12500 13100 13100 13100 Month Name JAN FEB MAR JAN FEB MAR Beans 5 8 1 6 3 2 Bread 11 7 2 4 2 0 Jam 7 3 0 4 1 0 Total 22 18 3 14 6 2 Then I think you'd be better off changing your layout to this: Product ID Month Qty Beans 12500 JAN 5 Beans 12500 FEB 8 Beans 12500 MAR 1 Beans 13100 JAN 6 Beans 13100 FEB 3 Beans 13100 MAR 2 Bread 12500 JAN 11 Bread 12500 FEB 7 Bread 12500 MAR 2 Bread 13100 JAN 4 Bread 13100 FEB 2 Bread 13100 MAR 0 Jam 12500 JAN 7 Jam 12500 FEB 3 Jam 12500 MAR 0 Jam 13100 JAN 4 Jam 13100 FEB 1 Jam 13100 MAR 0 After the data is laid out like this, you can use data|pivottable to get very nice (and quick) summaries of what you want. But the problem is the format of the data coming to you. (Or is that just a problem in your post to the newsgroup????) In any case, if you can clean up your data first, you could use a routine like this to change your data: Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim iCol As Long Dim FirstCol As Long Dim LastCol As Long Dim oRow As Long Set CurWks = Worksheets("sheet1") Set NewWks = Worksheets.Add NewWks.Range("a1").Resize(1, 4).Value _ = Array("Product", "ID", "Month", "Qty") oRow = 2 With CurWks FirstRow = 3 'headers in rows 1 and 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row FirstCol = 2 LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column For iRow = FirstRow To LastRow For iCol = FirstCol To LastCol If LCase(.Cells(iRow, "A").Value) = LCase("total") Then 'do nothing Else NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value NewWks.Cells(oRow, "B").Value = .Cells(1, iCol).Value NewWks.Cells(oRow, "C").Value = .Cells(2, iCol).Value NewWks.Cells(oRow, "D").Value = .Cells(iRow, iCol).Value oRow = oRow + 1 End If Next iCol Next iRow End With NewWks.UsedRange.Columns.AutoFit End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm And now after you have your data nicely "tabularized", you can use data|pivottable to create those summaries. If you've never use pivottables, here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Carl wrote: Each week I get a huge amount of data sent to me in the format below 12500 12500 12500 13100 13100 13100 User Number JAN FEB MAR JAN FEB MAR Month Name Beans 5 8 1 6 3 2 Bread 11 7 2 4 2 0 Jam 7 3 0 4 1 0 Total 22 18 3 14 6 2 So from this table I can see that USER 12500 sold 5 Beans in Jan, 8 in Feb etc I want to present the data with the user on the left, the products along side it, like : Jan Jan Jan Feb Feb Feb Beans Bread Jam Beans Bread Jam 12500 5 11 7 8 7 3 13100 6 4 4 etc At first I though I could transpose the data when pasting but that isnt working. I am scratching my head as I dont know if I would be best using a lookup, and index, IF's? Any advice on the best way to tackle it? Many thanks! Carl. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Not sure what to use? Lookup / Index / Match etc
GULP!!!
I will ingest that and get stuck into it tomorrow! Nearly finish time in the UK now. Thanks Dave. "Dave Peterson" wrote in message ... First, this may not be the way to go. But if your data were laid out nicer--no gaps in column A--like: User Number 12500 12500 12500 13100 13100 13100 Month Name JAN FEB MAR JAN FEB MAR Beans 5 8 1 6 3 2 Bread 11 7 2 4 2 0 Jam 7 3 0 4 1 0 Total 22 18 3 14 6 2 Then I think you'd be better off changing your layout to this: Product ID Month Qty Beans 12500 JAN 5 Beans 12500 FEB 8 Beans 12500 MAR 1 Beans 13100 JAN 6 Beans 13100 FEB 3 Beans 13100 MAR 2 Bread 12500 JAN 11 Bread 12500 FEB 7 Bread 12500 MAR 2 Bread 13100 JAN 4 Bread 13100 FEB 2 Bread 13100 MAR 0 Jam 12500 JAN 7 Jam 12500 FEB 3 Jam 12500 MAR 0 Jam 13100 JAN 4 Jam 13100 FEB 1 Jam 13100 MAR 0 After the data is laid out like this, you can use data|pivottable to get very nice (and quick) summaries of what you want. But the problem is the format of the data coming to you. (Or is that just a problem in your post to the newsgroup????) In any case, if you can clean up your data first, you could use a routine like this to change your data: Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim iCol As Long Dim FirstCol As Long Dim LastCol As Long Dim oRow As Long Set CurWks = Worksheets("sheet1") Set NewWks = Worksheets.Add NewWks.Range("a1").Resize(1, 4).Value _ = Array("Product", "ID", "Month", "Qty") oRow = 2 With CurWks FirstRow = 3 'headers in rows 1 and 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row FirstCol = 2 LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column For iRow = FirstRow To LastRow For iCol = FirstCol To LastCol If LCase(.Cells(iRow, "A").Value) = LCase("total") Then 'do nothing Else NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value NewWks.Cells(oRow, "B").Value = .Cells(1, iCol).Value NewWks.Cells(oRow, "C").Value = .Cells(2, iCol).Value NewWks.Cells(oRow, "D").Value = .Cells(iRow, iCol).Value oRow = oRow + 1 End If Next iCol Next iRow End With NewWks.UsedRange.Columns.AutoFit End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm And now after you have your data nicely "tabularized", you can use data|pivottable to create those summaries. If you've never use pivottables, here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Carl wrote: Each week I get a huge amount of data sent to me in the format below 12500 12500 12500 13100 13100 13100 User Number JAN FEB MAR JAN FEB MAR Month Name Beans 5 8 1 6 3 2 Bread 11 7 2 4 2 0 Jam 7 3 0 4 1 0 Total 22 18 3 14 6 2 So from this table I can see that USER 12500 sold 5 Beans in Jan, 8 in Feb etc I want to present the data with the user on the left, the products along side it, like : Jan Jan Jan Feb Feb Feb Beans Bread Jam Beans Bread Jam 12500 5 11 7 8 7 3 13100 6 4 4 etc At first I though I could transpose the data when pasting but that isnt working. I am scratching my head as I dont know if I would be best using a lookup, and index, IF's? Any advice on the best way to tackle it? Many thanks! Carl. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
index match lookup | New Users to Excel | |||
Lookup/match/index | Excel Discussion (Misc queries) | |||
Match Index Lookup | Excel Discussion (Misc queries) | |||
index / match /lookup ? help | Excel Worksheet Functions | |||
lookup (v,h,index,match) | Excel Worksheet Functions |