Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
index match lookup rplp81 New Users to Excel 1 November 15th 06 08:23 PM
Lookup/match/index Jules Excel Discussion (Misc queries) 1 July 5th 06 05:02 PM
Match Index Lookup Kevin Excel Discussion (Misc queries) 1 March 17th 06 04:40 PM
index / match /lookup ? help andrewm Excel Worksheet Functions 8 July 21st 05 02:55 PM
lookup (v,h,index,match) briank Excel Worksheet Functions 1 February 9th 05 02:05 AM


All times are GMT +1. The time now is 02:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"