Combine VLOOKUP, IF, AND in one formula

[Natalie]

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]

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

[JulieD]

Hi

would the following structure be acceptable:
.......................Dec 04........................Jan 05
Name.........Electric.....Gas..................Electric....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" <Natalie@discussions.microsoft.com> wrote in message
news:CC0AFAAB-2B63-4102-A533-793258DAD2DC@microsoft.com...
> 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

[Natalie]

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
>
>

[Natalie]

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,'Sheet
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..................Electric....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" <Natalie@discussions.microsoft.com> wrote in message
> news:CC0AFAAB-2B63-4102-A533-793258DAD2DC@microsoft.com...
> > 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
>
>
>

[Arvi Laanemets]

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",""),"")),Sheet1!$D$2:$D$5)

and copy the formula into range B2:E3

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


"Natalie" <Natalie@discussions.microsoft.com> wrote in message
news:CC0AFAAB-2B63-4102-A533-793258DAD2DC@microsoft.com...
> 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