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
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 quot;dataquot; 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
quot;Joequot; wrote:
gt; Hello
gt;
gt; I have three coloums of data which I want to put into a table format (below)
gt; COL A as the rows, COL B as the column headers and COL C as the data
gt;
gt; I have tried using a pivot table but the Data needs to be numeric, therefore
gt; doesn't work. The same with sumproduct
gt;
gt; I then tried using the following formula
gt; =IF(AND(Data!$A:$A=Summary!$A3,Data!$B:$B=Summary! C$2),lookup,Data!$C:$C)
gt; (with the data being on one sheet and the table being on a second sheet)
gt;
gt; but it only seems to be doing the same as VLOOKUP and picking up the first
gt; in COL C list using COL A as the reference.
gt;
gt; I tried using ctrl-shift-enter but the formula just returned the result -
gt; #NUM!
gt;
gt;
gt; Does anybody have any ideas?
gt; Thanks
gt;
gt; COL A COL B COL C
gt; 29/50/100 Balance Sheet 05
gt; 29/50/200 Balance Sheet 05
gt; 32/20/000 Balance Sheet 09
gt; 34/10/000 Balance Sheet 10
gt; 29/50/100 Budget Managers MM
gt; 29/50/200 Budget Managers MM
gt; 32/20/000 Budget Managers ZZ
gt; 34/10/000 Budget Managers ZZ
gt; 29/50/100 Contact Care ZZ
gt; 29/50/200 Contact Care ZZ
gt; 32/20/000 Contact Care ZZ
gt; 34/10/000 Contact Care ZZ
gt; 29/50/100 Copy to Repairs N
gt; 29/50/200 Copy to Repairs N
gt; 32/20/000 Copy to Repairs N
gt; 34/10/000 Copy to Repairs N
gt; 29/50/100 Fixed Assets rp ZZ
gt; 29/50/200 Fixed Assets rp ZZ
gt; 32/20/000 Fixed Assets rp ZZ
gt; 34/10/000 Fixed Assets rp ZZ
gt; 29/50/100 General IE ZZ
gt; 29/50/200 General IE ZZ
gt; 32/20/000 General IE ZZ
gt; 34/10/000 General IE ZZ
gt; 29/50/100 Housing Summary ZZ
gt; 29/50/200 Housing Summary ZZ
gt; 32/20/000 Housing Summary ZZ
gt; 34/10/000 Housing Summary ZZ
gt; 29/50/100 Recharge ZZ
gt; 29/50/200 Recharge ZZ
gt; 32/20/000 Recharge ZZ
gt; 34/10/000 Recharge ZZ
gt; 34/10/000 VAT Details CN
gt; 29/50/100 VAT Details ZZ
gt; 29/50/200 VAT Details ZZ
gt; 32/20/000 VAT Details ZZ
gt;
gt;
gt;
I think can make the Pivot Table work for you if you use quot;Count ofquot;, instead
of quot;Sum ofquot;. Then it can work won't mind non-numeric data.
Does that help?
***********
Regards,
Ron
XL2002, WinXP-Proquot;Joequot; wrote:
gt; Hello
gt;
gt; I have three coloums of data which I want to put into a table format (below)
gt; COL A as the rows, COL B as the column headers and COL C as the data
gt;
gt; I have tried using a pivot table but the Data needs to be numeric, therefore
gt; doesn't work. The same with sumproduct
gt;
gt; I then tried using the following formula
gt; =IF(AND(Data!$A:$A=Summary!$A3,Data!$B:$B=Summary! C$2),lookup,Data!$C:$C)
gt; (with the data being on one sheet and the table being on a second sheet)
gt;
gt; but it only seems to be doing the same as VLOOKUP and picking up the first
gt; in COL C list using COL A as the reference.
gt;
gt; I tried using ctrl-shift-enter but the formula just returned the result -
gt; #NUM!
gt;
gt;
gt; Does anybody have any ideas?
gt; Thanks
gt;
gt; COL A COL B COL C
gt; 29/50/100 Balance Sheet 05
gt; 29/50/200 Balance Sheet 05
gt; 32/20/000 Balance Sheet 09
gt; 34/10/000 Balance Sheet 10
gt; 29/50/100 Budget Managers MM
gt; 29/50/200 Budget Managers MM
gt; 32/20/000 Budget Managers ZZ
gt; 34/10/000 Budget Managers ZZ
gt; 29/50/100 Contact Care ZZ
gt; 29/50/200 Contact Care ZZ
gt; 32/20/000 Contact Care ZZ
gt; 34/10/000 Contact Care ZZ
gt; 29/50/100 Copy to Repairs N
gt; 29/50/200 Copy to Repairs N
gt; 32/20/000 Copy to Repairs N
gt; 34/10/000 Copy to Repairs N
gt; 29/50/100 Fixed Assets rp ZZ
gt; 29/50/200 Fixed Assets rp ZZ
gt; 32/20/000 Fixed Assets rp ZZ
gt; 34/10/000 Fixed Assets rp ZZ
gt; 29/50/100 General IE ZZ
gt; 29/50/200 General IE ZZ
gt; 32/20/000 General IE ZZ
gt; 34/10/000 General IE ZZ
gt; 29/50/100 Housing Summary ZZ
gt; 29/50/200 Housing Summary ZZ
gt; 32/20/000 Housing Summary ZZ
gt; 34/10/000 Housing Summary ZZ
gt; 29/50/100 Recharge ZZ
gt; 29/50/200 Recharge ZZ
gt; 32/20/000 Recharge ZZ
gt; 34/10/000 Recharge ZZ
gt; 34/10/000 VAT Details CN
gt; 29/50/100 VAT Details ZZ
gt; 29/50/200 VAT Details ZZ
gt; 32/20/000 VAT Details ZZ
gt;
gt;
gt;
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 amp; col B as reference.
Thanks
quot;William Hortonquot; gt; wrote in message
news
gt; 1) Create a new worksheet.
gt; 2) Copy column A from the old worksheet into the new worksheet starting
gt; in
gt; cell A2 and go down from there.
gt; 3) In cell B1 of the new worksheet and over to the right for as many
gt; columns as there are rows on the old worksheet use the TRANSPOSE function
gt; to
gt; get the column B values to go across row 1 as column headings. (After you
gt; use the TRANSPOSE function you may want to copy and paste special values.
gt; 4) Then in all the quot;dataquot; area cells of the new worksheet you can use the
gt; INDEX function in combination of the VLOOKUP function to get the data in
gt; the
gt; proper cells.
gt;
gt; Hope this is a start.
gt;
gt; Bill Horton
gt;
gt; quot;Joequot; wrote:
gt;
gt;gt; Hello
gt;gt;
gt;gt; I have three coloums of data which I want to put into a table format
gt;gt; (below)
gt;gt; COL A as the rows, COL B as the column headers and COL C as the data
gt;gt;
gt;gt; I have tried using a pivot table but the Data needs to be numeric,
gt;gt; therefore
gt;gt; doesn't work. The same with sumproduct
gt;gt;
gt;gt; I then tried using the following formula
gt;gt; =IF(AND(Data!$A:$A=Summary!$A3,Data!$B:$B=Summary! C$2),lookup,Data!$C:$C)
gt;gt; (with the data being on one sheet and the table being on a second sheet)
gt;gt;
gt;gt; but it only seems to be doing the same as VLOOKUP and picking up the
gt;gt; first
gt;gt; in COL C list using COL A as the reference.
gt;gt;
gt;gt; I tried using ctrl-shift-enter but the formula just returned the result -
gt;gt; #NUM!
gt;gt;
gt;gt;
gt;gt; Does anybody have any ideas?
gt;gt; Thanks
gt;gt;
gt;gt; COL A COL B COL C
gt;gt; 29/50/100 Balance Sheet 05
gt;gt; 29/50/200 Balance Sheet 05
gt;gt; 32/20/000 Balance Sheet 09
gt;gt; 34/10/000 Balance Sheet 10
gt;gt; 29/50/100 Budget Managers MM
gt;gt; 29/50/200 Budget Managers MM
gt;gt; 32/20/000 Budget Managers ZZ
gt;gt; 34/10/000 Budget Managers ZZ
gt;gt; 29/50/100 Contact Care ZZ
gt;gt; 29/50/200 Contact Care ZZ
gt;gt; 32/20/000 Contact Care ZZ
gt;gt; 34/10/000 Contact Care ZZ
gt;gt; 29/50/100 Copy to Repairs N
gt;gt; 29/50/200 Copy to Repairs N
gt;gt; 32/20/000 Copy to Repairs N
gt;gt; 34/10/000 Copy to Repairs N
gt;gt; 29/50/100 Fixed Assets rp ZZ
gt;gt; 29/50/200 Fixed Assets rp ZZ
gt;gt; 32/20/000 Fixed Assets rp ZZ
gt;gt; 34/10/000 Fixed Assets rp ZZ
gt;gt; 29/50/100 General IE ZZ
gt;gt; 29/50/200 General IE ZZ
gt;gt; 32/20/000 General IE ZZ
gt;gt; 34/10/000 General IE ZZ
gt;gt; 29/50/100 Housing Summary ZZ
gt;gt; 29/50/200 Housing Summary ZZ
gt;gt; 32/20/000 Housing Summary ZZ
gt;gt; 34/10/000 Housing Summary ZZ
gt;gt; 29/50/100 Recharge ZZ
gt;gt; 29/50/200 Recharge ZZ
gt;gt; 32/20/000 Recharge ZZ
gt;gt; 34/10/000 Recharge ZZ
gt;gt; 34/10/000 VAT Details CN
gt;gt; 29/50/100 VAT Details ZZ
gt;gt; 29/50/200 VAT Details ZZ
gt;gt; 32/20/000 VAT Details ZZ
gt;gt;
gt;gt;
gt;gt;
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 againquot;Ron Coderrequot; gt; wrote in message
...
gt;I think can make the Pivot Table work for you if you use quot;Count ofquot;,
gt;instead
gt; of quot;Sum ofquot;. Then it can work won't mind non-numeric data.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Joequot; wrote:
gt;
gt;gt; Hello
gt;gt;
gt;gt; I have three coloums of data which I want to put into a table format
gt;gt; (below)
gt;gt; COL A as the rows, COL B as the column headers and COL C as the data
gt;gt;
gt;gt; I have tried using a pivot table but the Data needs to be numeric,
gt;gt; therefore
gt;gt; doesn't work. The same with sumproduct
gt;gt;
gt;gt; I then tried using the following formula
gt;gt; =IF(AND(Data!$A:$A=Summary!$A3,Data!$B:$B=Summary! C$2),lookup,Data!$C:$C)
gt;gt; (with the data being on one sheet and the table being on a second sheet)
gt;gt;
gt;gt; but it only seems to be doing the same as VLOOKUP and picking up the
gt;gt; first
gt;gt; in COL C list using COL A as the reference.
gt;gt;
gt;gt; I tried using ctrl-shift-enter but the formula just returned the result -
gt;gt; #NUM!
gt;gt;
gt;gt;
gt;gt; Does anybody have any ideas?
gt;gt; Thanks
gt;gt;
gt;gt; COL A COL B COL C
gt;gt; 29/50/100 Balance Sheet 05
gt;gt; 29/50/200 Balance Sheet 05
gt;gt; 32/20/000 Balance Sheet 09
gt;gt; 34/10/000 Balance Sheet 10
gt;gt; 29/50/100 Budget Managers MM
gt;gt; 29/50/200 Budget Managers MM
gt;gt; 32/20/000 Budget Managers ZZ
gt;gt; 34/10/000 Budget Managers ZZ
gt;gt; 29/50/100 Contact Care ZZ
gt;gt; 29/50/200 Contact Care ZZ
gt;gt; 32/20/000 Contact Care ZZ
gt;gt; 34/10/000 Contact Care ZZ
gt;gt; 29/50/100 Copy to Repairs N
gt;gt; 29/50/200 Copy to Repairs N
gt;gt; 32/20/000 Copy to Repairs N
gt;gt; 34/10/000 Copy to Repairs N
gt;gt; 29/50/100 Fixed Assets rp ZZ
gt;gt; 29/50/200 Fixed Assets rp ZZ
gt;gt; 32/20/000 Fixed Assets rp ZZ
gt;gt; 34/10/000 Fixed Assets rp ZZ
gt;gt; 29/50/100 General IE ZZ
gt;gt; 29/50/200 General IE ZZ
gt;gt; 32/20/000 General IE ZZ
gt;gt; 34/10/000 General IE ZZ
gt;gt; 29/50/100 Housing Summary ZZ
gt;gt; 29/50/200 Housing Summary ZZ
gt;gt; 32/20/000 Housing Summary ZZ
gt;gt; 34/10/000 Housing Summary ZZ
gt;gt; 29/50/100 Recharge ZZ
gt;gt; 29/50/200 Recharge ZZ
gt;gt; 32/20/000 Recharge ZZ
gt;gt; 34/10/000 Recharge ZZ
gt;gt; 34/10/000 VAT Details CN
gt;gt; 29/50/100 VAT Details ZZ
gt;gt; 29/50/200 VAT Details ZZ
gt;gt; 32/20/000 VAT Details ZZ
gt;gt;
gt;gt;
gt;gt;
See my post on Dec 19
tinyurl.com/8xdjlIn 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 quot;Count ofquot; field in the DATA area.
I'm not sure if that applies in your situation.
***********
Regards,
Ron
XL2002, WinXP-Proquot;Joequot; wrote:
gt; Hi
gt;
gt; thank you for your answer
gt;
gt; I have tried this but the count function will only count if there data in
gt; the cells, I need the data from the cells.
gt;
gt; thanks again
gt;
gt;
gt; quot;Ron Coderrequot; gt; wrote in message
gt; ...
gt; gt;I think can make the Pivot Table work for you if you use quot;Count ofquot;,
gt; gt;instead
gt; gt; of quot;Sum ofquot;. Then it can work won't mind non-numeric data.
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Joequot; wrote:
gt; gt;
gt; gt;gt; Hello
gt; gt;gt;
gt; gt;gt; I have three coloums of data which I want to put into a table format
gt; gt;gt; (below)
gt; gt;gt; COL A as the rows, COL B as the column headers and COL C as the data
gt; gt;gt;
gt; gt;gt; I have tried using a pivot table but the Data needs to be numeric,
gt; gt;gt; therefore
gt; gt;gt; doesn't work. The same with sumproduct
gt; gt;gt;
gt; gt;gt; I then tried using the following formula
gt; gt;gt; =IF(AND(Data!$A:$A=Summary!$A3,Data!$B:$B=Summary! C$2),lookup,Data!$C:$C)
gt; gt;gt; (with the data being on one sheet and the table being on a second sheet)
gt; gt;gt;
gt; gt;gt; but it only seems to be doing the same as VLOOKUP and picking up the
gt; gt;gt; first
gt; gt;gt; in COL C list using COL A as the reference.
gt; gt;gt;
gt; gt;gt; I tried using ctrl-shift-enter but the formula just returned the result -
gt; gt;gt; #NUM!
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; Does anybody have any ideas?
gt; gt;gt; Thanks
gt; gt;gt;
gt; gt;gt; COL A COL B COL C
gt; gt;gt; 29/50/100 Balance Sheet 05
gt; gt;gt; 29/50/200 Balance Sheet 05
gt; gt;gt; 32/20/000 Balance Sheet 09
gt; gt;gt; 34/10/000 Balance Sheet 10
gt; gt;gt; 29/50/100 Budget Managers MM
gt; gt;gt; 29/50/200 Budget Managers MM
gt; gt;gt; 32/20/000 Budget Managers ZZ
gt; gt;gt; 34/10/000 Budget Managers ZZ
gt; gt;gt; 29/50/100 Contact Care ZZ
gt; gt;gt; 29/50/200 Contact Care ZZ
gt; gt;gt; 32/20/000 Contact Care ZZ
gt; gt;gt; 34/10/000 Contact Care ZZ
gt; gt;gt; 29/50/100 Copy to Repairs N
gt; gt;gt; 29/50/200 Copy to Repairs N
gt; gt;gt; 32/20/000 Copy to Repairs N
gt; gt;gt; 34/10/000 Copy to Repairs N
gt; gt;gt; 29/50/100 Fixed Assets rp ZZ
gt; gt;gt; 29/50/200 Fixed Assets rp ZZ
gt; gt;gt; 32/20/000 Fixed Assets rp ZZ
gt; gt;gt; 34/10/000 Fixed Assets rp ZZ
gt; gt;gt; 29/50/100 General IE ZZ
gt; gt;gt; 29/50/200 General IE ZZ
gt; gt;gt; 32/20/000 General IE ZZ
gt; gt;gt; 34/10/000 General IE ZZ
gt; gt;gt; 29/50/100 Housing Summary ZZ
gt; gt;gt; 29/50/200 Housing Summary ZZ
gt; gt;gt; 32/20/000 Housing Summary ZZ
gt; gt;gt; 34/10/000 Housing Summary ZZ
gt; gt;gt; 29/50/100 Recharge ZZ
gt; gt;gt; 29/50/200 Recharge ZZ
gt; gt;gt; 32/20/000 Recharge ZZ
gt; gt;gt; 34/10/000 Recharge ZZ
gt; gt;gt; 34/10/000 VAT Details CN
gt; gt;gt; 29/50/100 VAT Details ZZ
gt; gt;gt; 29/50/200 VAT Details ZZ
gt; gt;gt; 32/20/000 VAT Details ZZ
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;
留言列表
