lookup? list data into summary table

Joe · 12-21-2005, 04:40 PM

Hello

I have three coloums of data which I want to put into a table format (below)
COL A as the rows, COL B as the column headers and COL C as the data

I have tried using a pivot table but the Data needs to be numeric, therefore
doesn't work. The same with sumproduct

I then tried using the following formula
=IF(AND(Data!$A:$A=Summary!$A3,Data!$B:$B=Summary!C$2),lookup,Data!$C:$C)
(with the data being on one sheet and the table being on a second sheet)

but it only seems to be doing the same as VLOOKUP and picking up the first
in COL C list using COL A as the reference.

I tried using ctrl-shift-enter but the formula just returned the result -
#NUM!

Does anybody have any ideas?
Thanks

COL A COL B COL C
29/50/100 Balance Sheet 05
29/50/200 Balance Sheet 05
32/20/000 Balance Sheet 09
34/10/000 Balance Sheet 10
29/50/100 Budget Managers MM
29/50/200 Budget Managers MM
32/20/000 Budget Managers ZZ
34/10/000 Budget Managers ZZ
29/50/100 Contact Care ZZ
29/50/200 Contact Care ZZ
32/20/000 Contact Care ZZ
34/10/000 Contact Care ZZ
29/50/100 Copy to Repairs N
29/50/200 Copy to Repairs N
32/20/000 Copy to Repairs N
34/10/000 Copy to Repairs N
29/50/100 Fixed Assets rp ZZ
29/50/200 Fixed Assets rp ZZ
32/20/000 Fixed Assets rp ZZ
34/10/000 Fixed Assets rp ZZ
29/50/100 General IE ZZ
29/50/200 General IE ZZ
32/20/000 General IE ZZ
34/10/000 General IE ZZ
29/50/100 Housing Summary ZZ
29/50/200 Housing Summary ZZ
32/20/000 Housing Summary ZZ
34/10/000 Housing Summary ZZ
29/50/100 Recharge ZZ
29/50/200 Recharge ZZ
32/20/000 Recharge ZZ
34/10/000 Recharge ZZ
34/10/000 VAT Details CN
29/50/100 VAT Details ZZ
29/50/200 VAT Details ZZ
32/20/000 VAT Details ZZ

William Horton · 12-21-2005, 05:00 PM

1) Create a new worksheet.
2) Copy column A from the old worksheet into the new worksheet starting in
cell A2 and go down from there.
3) In cell B1 of the new worksheet and over to the right for as many
columns as there are rows on the old worksheet use the TRANSPOSE function to
get the column B values to go across row 1 as column headings. (After you
use the TRANSPOSE function you may want to copy and paste special values.
4) Then in all the "data" area cells of the new worksheet you can use the
INDEX function in combination of the VLOOKUP function to get the data in the
proper cells.

Hope this is a start.

Bill Horton

"Joe" wrote:

> Hello
>
> I have three coloums of data which I want to put into a table format (below)
> COL A as the rows, COL B as the column headers and COL C as the data
>
> I have tried using a pivot table but the Data needs to be numeric, therefore
> doesn't work. The same with sumproduct
>
> I then tried using the following formula
> =IF(AND(Data!$A:$A=Summary!$A3,Data!$B:$B=Summary!C$2),lookup,Data!$C:$C)
> (with the data being on one sheet and the table being on a second sheet)
>
> but it only seems to be doing the same as VLOOKUP and picking up the first
> in COL C list using COL A as the reference.
>
> I tried using ctrl-shift-enter but the formula just returned the result -
> #NUM!
>
>
> Does anybody have any ideas?
> Thanks
>
> COL A COL B COL C
> 29/50/100 Balance Sheet 05
> 29/50/200 Balance Sheet 05
> 32/20/000 Balance Sheet 09
> 34/10/000 Balance Sheet 10
> 29/50/100 Budget Managers MM
> 29/50/200 Budget Managers MM
> 32/20/000 Budget Managers ZZ
> 34/10/000 Budget Managers ZZ
> 29/50/100 Contact Care ZZ
> 29/50/200 Contact Care ZZ
> 32/20/000 Contact Care ZZ
> 34/10/000 Contact Care ZZ
> 29/50/100 Copy to Repairs N
> 29/50/200 Copy to Repairs N
> 32/20/000 Copy to Repairs N
> 34/10/000 Copy to Repairs N
> 29/50/100 Fixed Assets rp ZZ
> 29/50/200 Fixed Assets rp ZZ
> 32/20/000 Fixed Assets rp ZZ
> 34/10/000 Fixed Assets rp ZZ
> 29/50/100 General IE ZZ
> 29/50/200 General IE ZZ
> 32/20/000 General IE ZZ
> 34/10/000 General IE ZZ
> 29/50/100 Housing Summary ZZ
> 29/50/200 Housing Summary ZZ
> 32/20/000 Housing Summary ZZ
> 34/10/000 Housing Summary ZZ
> 29/50/100 Recharge ZZ
> 29/50/200 Recharge ZZ
> 32/20/000 Recharge ZZ
> 34/10/000 Recharge ZZ
> 34/10/000 VAT Details CN
> 29/50/100 VAT Details ZZ
> 29/50/200 VAT Details ZZ
> 32/20/000 VAT Details ZZ
>
>
>

Ron Coderre · 12-21-2005, 05:10 PM

I think can make the Pivot Table work for you if you use "Count of", instead
of "Sum of". Then it can work won't mind non-numeric data.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

"Joe" wrote:

> Hello
>
> I have three coloums of data which I want to put into a table format (below)
> COL A as the rows, COL B as the column headers and COL C as the data
>
> I have tried using a pivot table but the Data needs to be numeric, therefore
> doesn't work. The same with sumproduct
>
> I then tried using the following formula
> =IF(AND(Data!$A:$A=Summary!$A3,Data!$B:$B=Summary!C$2),lookup,Data!$C:$C)
> (with the data being on one sheet and the table being on a second sheet)
>
> but it only seems to be doing the same as VLOOKUP and picking up the first
> in COL C list using COL A as the reference.
>
> I tried using ctrl-shift-enter but the formula just returned the result -
> #NUM!
>
>
> Does anybody have any ideas?
> Thanks
>
> COL A COL B COL C
> 29/50/100 Balance Sheet 05
> 29/50/200 Balance Sheet 05
> 32/20/000 Balance Sheet 09
> 34/10/000 Balance Sheet 10
> 29/50/100 Budget Managers MM
> 29/50/200 Budget Managers MM
> 32/20/000 Budget Managers ZZ
> 34/10/000 Budget Managers ZZ
> 29/50/100 Contact Care ZZ
> 29/50/200 Contact Care ZZ
> 32/20/000 Contact Care ZZ
> 34/10/000 Contact Care ZZ
> 29/50/100 Copy to Repairs N
> 29/50/200 Copy to Repairs N
> 32/20/000 Copy to Repairs N
> 34/10/000 Copy to Repairs N
> 29/50/100 Fixed Assets rp ZZ
> 29/50/200 Fixed Assets rp ZZ
> 32/20/000 Fixed Assets rp ZZ
> 34/10/000 Fixed Assets rp ZZ
> 29/50/100 General IE ZZ
> 29/50/200 General IE ZZ
> 32/20/000 General IE ZZ
> 34/10/000 General IE ZZ
> 29/50/100 Housing Summary ZZ
> 29/50/200 Housing Summary ZZ
> 32/20/000 Housing Summary ZZ
> 34/10/000 Housing Summary ZZ
> 29/50/100 Recharge ZZ
> 29/50/200 Recharge ZZ
> 32/20/000 Recharge ZZ
> 34/10/000 Recharge ZZ
> 34/10/000 VAT Details CN
> 29/50/100 VAT Details ZZ
> 29/50/200 VAT Details ZZ
> 32/20/000 VAT Details ZZ
>
>
>

Joe · 12-21-2005, 05:25 PM

thank you for your fast answer.

I am not sure I fully understand. on the data sheet in col A I have 5418
rows, 602 unique cells, and in col B 9 unique cells.

I already have the summary table set up with 602 rows and 9 columns and I
want to table the data in col C using col A & col B as reference.

Thanks

"William Horton" <[email protected]> wrote in message
news:[email protected]...
> 1) Create a new worksheet.
> 2) Copy column A from the old worksheet into the new worksheet starting
> in
> cell A2 and go down from there.
> 3) In cell B1 of the new worksheet and over to the right for as many
> columns as there are rows on the old worksheet use the TRANSPOSE function
> to
> get the column B values to go across row 1 as column headings. (After you
> use the TRANSPOSE function you may want to copy and paste special values.
> 4) Then in all the "data" area cells of the new worksheet you can use the
> INDEX function in combination of the VLOOKUP function to get the data in
> the
> proper cells.
>
> Hope this is a start.
>
> Bill Horton
>
> "Joe" wrote:
>
>> Hello
>>
>> I have three coloums of data which I want to put into a table format
>> (below)
>> COL A as the rows, COL B as the column headers and COL C as the data
>>
>> I have tried using a pivot table but the Data needs to be numeric,
>> therefore
>> doesn't work. The same with sumproduct
>>
>> I then tried using the following formula
>> =IF(AND(Data!$A:$A=Summary!$A3,Data!$B:$B=Summary!C$2),lookup,Data!$C:$C)
>> (with the data being on one sheet and the table being on a second sheet)
>>
>> but it only seems to be doing the same as VLOOKUP and picking up the
>> first
>> in COL C list using COL A as the reference.
>>
>> I tried using ctrl-shift-enter but the formula just returned the result -
>> #NUM!
>>
>>
>> Does anybody have any ideas?
>> Thanks
>>
>> COL A COL B COL C
>> 29/50/100 Balance Sheet 05
>> 29/50/200 Balance Sheet 05
>> 32/20/000 Balance Sheet 09
>> 34/10/000 Balance Sheet 10
>> 29/50/100 Budget Managers MM
>> 29/50/200 Budget Managers MM
>> 32/20/000 Budget Managers ZZ
>> 34/10/000 Budget Managers ZZ
>> 29/50/100 Contact Care ZZ
>> 29/50/200 Contact Care ZZ
>> 32/20/000 Contact Care ZZ
>> 34/10/000 Contact Care ZZ
>> 29/50/100 Copy to Repairs N
>> 29/50/200 Copy to Repairs N
>> 32/20/000 Copy to Repairs N
>> 34/10/000 Copy to Repairs N
>> 29/50/100 Fixed Assets rp ZZ
>> 29/50/200 Fixed Assets rp ZZ
>> 32/20/000 Fixed Assets rp ZZ
>> 34/10/000 Fixed Assets rp ZZ
>> 29/50/100 General IE ZZ
>> 29/50/200 General IE ZZ
>> 32/20/000 General IE ZZ
>> 34/10/000 General IE ZZ
>> 29/50/100 Housing Summary ZZ
>> 29/50/200 Housing Summary ZZ
>> 32/20/000 Housing Summary ZZ
>> 34/10/000 Housing Summary ZZ
>> 29/50/100 Recharge ZZ
>> 29/50/200 Recharge ZZ
>> 32/20/000 Recharge ZZ
>> 34/10/000 Recharge ZZ
>> 34/10/000 VAT Details CN
>> 29/50/100 VAT Details ZZ
>> 29/50/200 VAT Details ZZ
>> 32/20/000 VAT Details ZZ
>>
>>
>>

Joe · 12-21-2005, 05:25 PM

Hi

thank you for your answer

I have tried this but the count function will only count if there data in
the cells, I need the data from the cells.

thanks again

"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
>I think can make the Pivot Table work for you if you use "Count of",
>instead
> of "Sum of". Then it can work won't mind non-numeric data.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Joe" wrote:
>
>> Hello
>>
>> I have three coloums of data which I want to put into a table format
>> (below)
>> COL A as the rows, COL B as the column headers and COL C as the data
>>
>> I have tried using a pivot table but the Data needs to be numeric,
>> therefore
>> doesn't work. The same with sumproduct
>>
>> I then tried using the following formula
>> =IF(AND(Data!$A:$A=Summary!$A3,Data!$B:$B=Summary!C$2),lookup,Data!$C:$C)
>> (with the data being on one sheet and the table being on a second sheet)
>>
>> but it only seems to be doing the same as VLOOKUP and picking up the
>> first
>> in COL C list using COL A as the reference.
>>
>> I tried using ctrl-shift-enter but the formula just returned the result -
>> #NUM!
>>
>>
>> Does anybody have any ideas?
>> Thanks
>>
>> COL A COL B COL C
>> 29/50/100 Balance Sheet 05
>> 29/50/200 Balance Sheet 05
>> 32/20/000 Balance Sheet 09
>> 34/10/000 Balance Sheet 10
>> 29/50/100 Budget Managers MM
>> 29/50/200 Budget Managers MM
>> 32/20/000 Budget Managers ZZ
>> 34/10/000 Budget Managers ZZ
>> 29/50/100 Contact Care ZZ
>> 29/50/200 Contact Care ZZ
>> 32/20/000 Contact Care ZZ
>> 34/10/000 Contact Care ZZ
>> 29/50/100 Copy to Repairs N
>> 29/50/200 Copy to Repairs N
>> 32/20/000 Copy to Repairs N
>> 34/10/000 Copy to Repairs N
>> 29/50/100 Fixed Assets rp ZZ
>> 29/50/200 Fixed Assets rp ZZ
>> 32/20/000 Fixed Assets rp ZZ
>> 34/10/000 Fixed Assets rp ZZ
>> 29/50/100 General IE ZZ
>> 29/50/200 General IE ZZ
>> 32/20/000 General IE ZZ
>> 34/10/000 General IE ZZ
>> 29/50/100 Housing Summary ZZ
>> 29/50/200 Housing Summary ZZ
>> 32/20/000 Housing Summary ZZ
>> 34/10/000 Housing Summary ZZ
>> 29/50/100 Recharge ZZ
>> 29/50/200 Recharge ZZ
>> 32/20/000 Recharge ZZ
>> 34/10/000 Recharge ZZ
>> 34/10/000 VAT Details CN
>> 29/50/100 VAT Details ZZ
>> 29/50/200 VAT Details ZZ
>> 32/20/000 VAT Details ZZ
>>
>>
>>

Herbert Seidenberg · 12-21-2005, 07:40 PM

See my post on Dec 19
http://tinyurl.com/8xdjl

Ron Coderre · 12-21-2005, 08:30 PM

In case you're still interested in a Pivot Table approach, you CAN use the
same field more than once in the Pivot Table: once as a row or column and
again as the "Count of" field in the DATA area.

I'm not sure if that applies in your situation.

***********
Regards,
Ron

XL2002, WinXP-Pro

"Joe" wrote:

> Hi
>
> thank you for your answer
>
> I have tried this but the count function will only count if there data in
> the cells, I need the data from the cells.
>
> thanks again
>
>
> "Ron Coderre" <[email protected]> wrote in message
> news:[email protected]...
> >I think can make the Pivot Table work for you if you use "Count of",
> >instead
> > of "Sum of". Then it can work won't mind non-numeric data.
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "Joe" wrote:
> >
> >> Hello
> >>
> >> I have three coloums of data which I want to put into a table format
> >> (below)
> >> COL A as the rows, COL B as the column headers and COL C as the data
> >>
> >> I have tried using a pivot table but the Data needs to be numeric,
> >> therefore
> >> doesn't work. The same with sumproduct
> >>
> >> I then tried using the following formula
> >> =IF(AND(Data!$A:$A=Summary!$A3,Data!$B:$B=Summary!C$2),lookup,Data!$C:$C)
> >> (with the data being on one sheet and the table being on a second sheet)
> >>
> >> but it only seems to be doing the same as VLOOKUP and picking up the
> >> first
> >> in COL C list using COL A as the reference.
> >>
> >> I tried using ctrl-shift-enter but the formula just returned the result -
> >> #NUM!
> >>
> >>
> >> Does anybody have any ideas?
> >> Thanks
> >>
> >> COL A COL B COL C
> >> 29/50/100 Balance Sheet 05
> >> 29/50/200 Balance Sheet 05
> >> 32/20/000 Balance Sheet 09
> >> 34/10/000 Balance Sheet 10
> >> 29/50/100 Budget Managers MM
> >> 29/50/200 Budget Managers MM
> >> 32/20/000 Budget Managers ZZ
> >> 34/10/000 Budget Managers ZZ
> >> 29/50/100 Contact Care ZZ
> >> 29/50/200 Contact Care ZZ
> >> 32/20/000 Contact Care ZZ
> >> 34/10/000 Contact Care ZZ
> >> 29/50/100 Copy to Repairs N
> >> 29/50/200 Copy to Repairs N
> >> 32/20/000 Copy to Repairs N
> >> 34/10/000 Copy to Repairs N
> >> 29/50/100 Fixed Assets rp ZZ
> >> 29/50/200 Fixed Assets rp ZZ
> >> 32/20/000 Fixed Assets rp ZZ
> >> 34/10/000 Fixed Assets rp ZZ
> >> 29/50/100 General IE ZZ
> >> 29/50/200 General IE ZZ
> >> 32/20/000 General IE ZZ
> >> 34/10/000 General IE ZZ
> >> 29/50/100 Housing Summary ZZ
> >> 29/50/200 Housing Summary ZZ
> >> 32/20/000 Housing Summary ZZ
> >> 34/10/000 Housing Summary ZZ
> >> 29/50/100 Recharge ZZ
> >> 29/50/200 Recharge ZZ
> >> 32/20/000 Recharge ZZ
> >> 34/10/000 Recharge ZZ
> >> 34/10/000 VAT Details CN
> >> 29/50/100 VAT Details ZZ
> >> 29/50/200 VAT Details ZZ
> >> 32/20/000 VAT Details ZZ
> >>
> >>
> >>
>
>
>