Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 5th 05, 01:15 PM
Natalie
 
Posts: n/a
Default Combine VLOOKUP, IF, AND in one formula

Hi,

I have one worksheet with information for lots of people, but on separate
rows -

Name Date Type of Bill Cost
Joe Bloggs Jan 05 Gas £10
Joe Bloggs Dec 05 Electric £6
Jemma Jan 05 Gas £2
Jemma Jan 05 Electric £5

I want to add them to a new sheet, which is set out as:

Name GasDec04 GasJan05 ElectricDec04 ElectricJan05
Joe Bloggs 0 £10 £6 0
Jemma 0 £2 0 £5

Can I use a lookup to do this?

Thanks

  #2   Report Post  
Old April 5th 05, 01:25 PM
Debra Dalgleish
 
Posts: n/a
Default

You can use a Pivot Table to summarize the data. There are instructions
in Excel's Help, and Jon Peltier has information and links:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Natalie wrote:
Hi,

I have one worksheet with information for lots of people, but on separate
rows -

Name Date Type of Bill Cost
Joe Bloggs Jan 05 Gas £10
Joe Bloggs Dec 05 Electric £6
Jemma Jan 05 Gas £2
Jemma Jan 05 Electric £5

I want to add them to a new sheet, which is set out as:

Name GasDec04 GasJan05 ElectricDec04 ElectricJan05
Joe Bloggs 0 £10 £6 0
Jemma 0 £2 0 £5

Can I use a lookup to do this?

Thanks



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #3   Report Post  
Old April 5th 05, 01:32 PM
JulieD
 
Posts: n/a
Default

Hi

would the following structure be acceptable:
.......................Dec 04........................Jan 05
Name.........Electric.....Gas..................Ele ctric....Gas
Joe Bloggs...6........................................ .............10
Jemma............................................. .....5.............2

if so, it can easily be achieved from your current data using a pivot table

click in your current data, choose data / pivot table and pivot chart report
NEXT
ensure that the whole range is selected NEXT
ensure new worksheet is selected and click LAYOUT
drag name to where it says "ROW"
drag date to where it says "COLUMN"
drag type of bill to where it says "COLUMN" ensuring it goes to the right of
date
drag cost to where it says "DATA"
click OK click FINISH

now right mouse click on Date and chose field settings, change automatic
subtotals to none
and you should have more or less what you're looking for.

-
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Natalie" wrote in message
...
Hi,

I have one worksheet with information for lots of people, but on separate
rows -

Name Date Type of Bill Cost
Joe Bloggs Jan 05 Gas 10
Joe Bloggs Dec 05 Electric 6
Jemma Jan 05 Gas 2
Jemma Jan 05 Electric 5

I want to add them to a new sheet, which is set out as:

Name GasDec04 GasJan05 ElectricDec04 ElectricJan05
Joe Bloggs 0 10 6 0
Jemma 0 2 0
5

Can I use a lookup to do this?

Thanks



  #4   Report Post  
Old April 5th 05, 01:33 PM
Natalie
 
Posts: n/a
Default

I know how to use Pivot tables, but I want this spreadsheet to be able to
filter through to other spreadsheets, and update automaticallly.

Is there any other way?

"Debra Dalgleish" wrote:

You can use a Pivot Table to summarize the data. There are instructions
in Excel's Help, and Jon Peltier has information and links:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Natalie wrote:
Hi,

I have one worksheet with information for lots of people, but on separate
rows -

Name Date Type of Bill Cost
Joe Bloggs Jan 05 Gas £10
Joe Bloggs Dec 05 Electric £6
Jemma Jan 05 Gas £2
Jemma Jan 05 Electric £5

I want to add them to a new sheet, which is set out as:

Name GasDec04 GasJan05 ElectricDec04 ElectricJan05
Joe Bloggs 0 £10 £6 0
Jemma 0 £2 0 £5

Can I use a lookup to do this?

Thanks



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


  #5   Report Post  
Old April 5th 05, 02:07 PM
Natalie
 
Posts: n/a
Default

I am adding this to an exsisting spreadsheet that already has information on
it, so I cannot use a Pivot table.

I am trying to use:

=IF(VLOOKUP(B2,'Sheet 1'!A1:F1262,5,FALSE)="Gas",AND,IF(VLOOKUP(B2,'Shee t
1'!A1:F1262,6,FALSE)="Dec-04",VLOOKUP(B2,'Sheet 1'!A1:F1262,2,FALSE)))

"JulieD" wrote:

Hi

would the following structure be acceptable:
.......................Dec 04........................Jan 05
Name.........Electric.....Gas..................Ele ctric....Gas
Joe Bloggs...6........................................ .............10
Jemma............................................. .....5.............2

if so, it can easily be achieved from your current data using a pivot table

click in your current data, choose data / pivot table and pivot chart report
NEXT
ensure that the whole range is selected NEXT
ensure new worksheet is selected and click LAYOUT
drag name to where it says "ROW"
drag date to where it says "COLUMN"
drag type of bill to where it says "COLUMN" ensuring it goes to the right of
date
drag cost to where it says "DATA"
click OK click FINISH

now right mouse click on Date and chose field settings, change automatic
subtotals to none
and you should have more or less what you're looking for.

-
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Natalie" wrote in message
...
Hi,

I have one worksheet with information for lots of people, but on separate
rows -

Name Date Type of Bill Cost
Joe Bloggs Jan 05 Gas £10
Joe Bloggs Dec 05 Electric £6
Jemma Jan 05 Gas £2
Jemma Jan 05 Electric £5

I want to add them to a new sheet, which is set out as:

Name GasDec04 GasJan05 ElectricDec04 ElectricJan05
Joe Bloggs 0 £10 £6 0
Jemma 0 £2 0
£5

Can I use a lookup to do this?

Thanks






  #6   Report Post  
Old April 5th 05, 02:32 PM
Arvi Laanemets
 
Posts: n/a
Default

Hi

Let's the first table be on Sheet1 with headers in row 1, and second table
on Sheet2, also with headers on row 1 (both tables staring from column A).

As first step, modify either entries in Date column on Sheet1, or headers on
Sheet2, so date text are identical. I.e. or you have dates in form "Jan05"
etc., or your headers are like "GasDec 04".

Now, into cell B2 on Sheet2 enter the formula
=SUMPRODUCT(--(Sheet1!$A$2:$A$5=$A2),--(Sheet1!$B$2:$B$5=SUBSTITUTE(SUBSTITU
TE(B$1,"Gas",""),"Electric","")),--(Sheet1!$C$2:$C$5=SUBSTITUTE(B$1,SUBSTITU
TE(SUBSTITUTE(B$1,"Gas",""),"Electric",""),"")),Sh eet1!$D$2:$D$5)

and copy the formula into range B2:E3

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Natalie" wrote in message
...
Hi,

I have one worksheet with information for lots of people, but on separate
rows -

Name Date Type of Bill Cost
Joe Bloggs Jan 05 Gas 10
Joe Bloggs Dec 05 Electric 6
Jemma Jan 05 Gas 2
Jemma Jan 05 Electric 5

I want to add them to a new sheet, which is set out as:

Name GasDec04 GasJan05 ElectricDec04 ElectricJan05
Joe Bloggs 0 10 6 0
Jemma 0 2 0

5

Can I use a lookup to do this?

Thanks





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
IF & VLOOKUP FORMULA taxmom Excel Worksheet Functions 3 March 2nd 05 03:35 PM
How do I combine the 4 IF formulas into 1 formula Robert Excel Worksheet Functions 5 February 7th 05 08:19 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 04:37 PM
formula to combine cells using if/then Roland Excel Worksheet Functions 1 January 25th 05 02:51 PM
How do I use Range Names listed in a VLookup table in a formula? Essbasedvlpr32 Excel Worksheet Functions 3 December 15th 04 10:11 PM


All times are GMT +1. The time now is 10:04 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017