|
lookup? list data into summary table
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 |
lookup? list data into summary table
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 |
lookup? list data into summary table
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 |
lookup? list data into summary table
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" wrote in message ... 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 |
lookup? list data into summary table
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" wrote in message ... 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 |
lookup? list data into summary table
|
lookup? list data into summary table
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" wrote in message ... 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 |
| All times are GMT +1. The time now is 01:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com