|
Summary display horizontally
I need helps for the summary from sheet 1 to sheet 2 to be displa
horizontally so that I view them all at a glance. The macro will alway execute from last row upward for the next 100 rows. Presently I use the auto filter method which cannot produce somethin as in sheet 2. Can it be done ? Kindly take a look at the example below. Sheet 1 (Datas) Rows===Col A 0001 A until 2000 A 2001 A 2002 C 2003 B 2004 A 2005 B 2006 B 2007 C 2008 C 2009 C 2010 A (e.g.last row) Sheet 2 (Report) Col A===Col B A 1,-5,1,-2,2 ....... B -4,2,-1,1 ......... C -1,3,-4,1,-2 ...... The value in col B read from Sheet 1 where Col A = Sheet 2 Col A fro bottom up.It record the counting from last row and continue unti skipping -100 rows of datas. e.g. the sheet 2 col B values were from row no 2010-2010+1=1, 2010-2005=-5,2005-2004=1,2004-2002=-2,2002-2000=2. The value in col B is positive when columns A of sheet 1 & sheet 2 ar equal else the value will becomes negative. I hope I can make it clear for someone to help me. Thanks Michae -- Message posted from http://www.ExcelForum.com |
Summary display horizontally
Hi
really not sure how you derived your values. Could you explain the logic behind your calculation? -- Regards Frank Kabel Frankfurt, Germany I need helps for the summary from sheet 1 to sheet 2 to be display horizontally so that I view them all at a glance. The macro will always execute from last row upward for the next 100 rows. Presently I use the auto filter method which cannot produce something as in sheet 2. Can it be done ? Kindly take a look at the example below. Sheet 1 (Datas) Rows===Col A 0001 A until 2000 A 2001 A 2002 C 2003 B 2004 A 2005 B 2006 B 2007 C 2008 C 2009 C 2010 A (e.g.last row) Sheet 2 (Report) Col A===Col B A 1,-5,1,-2,2 ....... B -4,2,-1,1 ......... C -1,3,-4,1,-2 ...... The value in col B read from Sheet 1 where Col A = Sheet 2 Col A from bottom up.It record the counting from last row and continue until skipping -100 rows of datas. e.g. the sheet 2 col B values were from row no 2010-2010+1=1, 2010-2005=-5,2005-2004=1,2004-2002=-2,2002-2000=2. The value in col B is positive when columns A of sheet 1 & sheet 2 are equal else the value will becomes negative. I hope I can make it clear for someone to help me. Thanks Michael --- Message posted from http://www.ExcelForum.com/ |
Summary display horizontally
Hi! Frank Kabel,
First of all I wish to thank you for trying to help me. I try t explain the logic as clear as possible and hopefully you can get th idea behind. In sheet 2, Col A contain the unrepeated value which can be found i col A of sheet 1. In sheet 2, col B values were derived from col B o sheet 1 appearing and skipping or in another word the gaps in between. Refer to my post, you can see them more clearly. Frank Kabel wrote: *Hi really not sure how you derived your values. Could you explain the logic behind your calculation? -- Regards Frank Kabel Frankfurt, Germany I need helps for the summary from sheet 1 to sheet 2 to be display horizontally so that I view them all at a glance. The macro will always execute from last row upward for the next 100 rows. Presently I use the auto filter method which cannot produc something as in sheet 2. Can it be done ? Kindly take a look at the example below. Sheet 1 (Datas) Rows===Col A 0001 A until 2000 A 2001 A 2002 C 2003 B 2004 A 2005 B 2006 B 2007 C 2008 C 2009 C 2010 A (e.g.last row) Sheet 2 (Report) Col A===Col B A 1,-5,1,-2,2 ....... B -4,2,-1,1 ......... C -1,3,-4,1,-2 ...... The value in col B read from Sheet 1 where Col A = Sheet 2 Col from bottom up.It record the counting from last row and continue until skipping -100 rows of datas. e.g. the sheet 2 col B values were from row no 2010-2010+1=1, 2010-2005=-5,2005-2004=1,2004-2002=-2,2002-2000=2. The value in col B is positive when columns A of sheet 1 & sheet 2 are equal else the value will becomes negative. I hope I can make it clear for someone to help me. Thanks Michael --- Message posted from http://www.ExcelForum.com/ -- Message posted from http://www.ExcelForum.com |
Summary display horizontally
Hi
I had a look at your post :-) But for example your first value is derived by the formula 2010-2010+1 why add '1' in this case. Your calculation just does not seem consistent for me (but that's probably just me). So you may explain this logic with some more detail. -- Regards Frank Kabel Frankfurt, Germany Hi! Frank Kabel, First of all I wish to thank you for trying to help me. I try to explain the logic as clear as possible and hopefully you can get the idea behind. In sheet 2, Col A contain the unrepeated value which can be found in col A of sheet 1. In sheet 2, col B values were derived from col B of sheet 1 appearing and skipping or in another word the gaps in between. Refer to my post, you can see them more clearly. Frank Kabel wrote: *Hi really not sure how you derived your values. Could you explain the logic behind your calculation? -- Regards Frank Kabel Frankfurt, Germany I need helps for the summary from sheet 1 to sheet 2 to be display horizontally so that I view them all at a glance. The macro will always execute from last row upward for the next 100 rows. Presently I use the auto filter method which cannot produce something as in sheet 2. Can it be done ? Kindly take a look at the example below. Sheet 1 (Datas) Rows===Col A 0001 A until 2000 A 2001 A 2002 C 2003 B 2004 A 2005 B 2006 B 2007 C 2008 C 2009 C 2010 A (e.g.last row) Sheet 2 (Report) Col A===Col B A 1,-5,1,-2,2 ....... B -4,2,-1,1 ......... C -1,3,-4,1,-2 ...... The value in col B read from Sheet 1 where Col A = Sheet 2 Col A from bottom up.It record the counting from last row and continue until skipping -100 rows of datas. e.g. the sheet 2 col B values were from row no 2010-2010+1=1, 2010-2005=-5,2005-2004=1,2004-2002=-2,2002-2000=2. The value in col B is positive when columns A of sheet 1 & sheet 2 are equal else the value will becomes negative. I hope I can make it clear for someone to help me. Thanks Michael --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ |
Summary display horizontally
Hi ! Frank Kabel,
I add 1 to 2010 because the last draw no in this example is 2010. need a 1 as the first starting value,that's why I use the 2010-2010+ to give me a value of 1. So if you take a look at the example below you will understand what mean. e.g. Sheet 2 (Report) Col A===Col B A=== 1,-5,1,-2,2 ....... The value will add 1 to the current value if the next value of col A i the same. Thanks Michael. Frank Kabel wrote: *Hi I had a look at your post :-) But for example your first value is derived by the formula 2010-2010+1 why add '1' in this case. Your calculation just does not seem consistent for me (but that's probably just me). So you may explain this logic with some more detail. -- Regards Frank Kabel Frankfurt, Germany Hi! Frank Kabel, First of all I wish to thank you for trying to help me. I try to explain the logic as clear as possible and hopefully you can ge the idea behind. In sheet 2, Col A contain the unrepeated value which can be foun in col A of sheet 1. In sheet 2, col B values were derived from col of sheet 1 appearing and skipping or in another word the gaps in between. Refer to my post, you can see them more clearly. Frank Kabel wrote: *Hi really not sure how you derived your values. Could you explai the logic behind your calculation? -- Regards Frank Kabel Frankfurt, Germany I need helps for the summary from sheet 1 to sheet 2 to b display horizontally so that I view them all at a glance. The macro will always execute from last row upward for the next 100 rows. Presently I use the auto filter method which cannot produce something as in sheet 2. Can it be done ? Kindly take a look at the example below. Sheet 1 (Datas) Rows===Col A 0001 A until 2000 A 2001 A 2002 C 2003 B 2004 A 2005 B 2006 B 2007 C 2008 C 2009 C 2010 A (e.g.last row) Sheet 2 (Report) Col A===Col B A 1,-5,1,-2,2 ....... B -4,2,-1,1 ......... C -1,3,-4,1,-2 ...... The value in col B read from Sheet 1 where Col A = Sheet 2 Col A from bottom up.It record the counting from last row and continue until skipping -100 rows of datas. e.g. the sheet 2 col B values were from row no 2010-2010+1=1, 2010-2005=-5,2005-2004=1,2004-2002=-2,2002-2000=2. The value in col B is positive when columns A of sheet 1 & shee 2 are equal else the value will becomes negative. I hope I can make it clear for someone to help me. Thanks Michael --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ -- Message posted from http://www.ExcelForum.com |
Summary display horizontally
Hi Michael
sorry to say but this is the same example I didn't get in the first place. So it's probably me who did not see the logic behind it. Maybe someone else sees the algorithmn -- Regards Frank Kabel Frankfurt, Germany Hi ! Frank Kabel, I add 1 to 2010 because the last draw no in this example is 2010. I need a 1 as the first starting value,that's why I use the 2010-2010+1 to give me a value of 1. So if you take a look at the example below you will understand what I mean. e.g. Sheet 2 (Report) Col A===Col B A=== 1,-5,1,-2,2 ....... The value will add 1 to the current value if the next value of col A is the same. Thanks Michael. Frank Kabel wrote: *Hi I had a look at your post :-) But for example your first value is derived by the formula 2010-2010+1 why add '1' in this case. Your calculation just does not seem consistent for me (but that's probably just me). So you may explain this logic with some more detail. -- Regards Frank Kabel Frankfurt, Germany Hi! Frank Kabel, First of all I wish to thank you for trying to help me. I try to explain the logic as clear as possible and hopefully you can get the idea behind. In sheet 2, Col A contain the unrepeated value which can be found in col A of sheet 1. In sheet 2, col B values were derived from col B of sheet 1 appearing and skipping or in another word the gaps in between. Refer to my post, you can see them more clearly. Frank Kabel wrote: *Hi really not sure how you derived your values. Could you explain the logic behind your calculation? -- Regards Frank Kabel Frankfurt, Germany I need helps for the summary from sheet 1 to sheet 2 to be display horizontally so that I view them all at a glance. The macro will always execute from last row upward for the next 100 rows. Presently I use the auto filter method which cannot produce something as in sheet 2. Can it be done ? Kindly take a look at the example below. Sheet 1 (Datas) Rows===Col A 0001 A until 2000 A 2001 A 2002 C 2003 B 2004 A 2005 B 2006 B 2007 C 2008 C 2009 C 2010 A (e.g.last row) Sheet 2 (Report) Col A===Col B A 1,-5,1,-2,2 ....... B -4,2,-1,1 ......... C -1,3,-4,1,-2 ...... The value in col B read from Sheet 1 where Col A = Sheet 2 Col A from bottom up.It record the counting from last row and continue until skipping -100 rows of datas. e.g. the sheet 2 col B values were from row no 2010-2010+1=1, 2010-2005=-5,2005-2004=1,2004-2002=-2,2002-2000=2. The value in col B is positive when columns A of sheet 1 & sheet 2 are equal else the value will becomes negative. I hope I can make it clear for someone to help me. Thanks Michael --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ |
Summary display horizontally
Michael,
As I said to the first posting previous to this posting and it remains consistent, this is almost unintelligible. You talk about matching numbers on two sheets, but only show a listing for one sheet, then show some kind of mysterious result on the other. You sometimes add a 1 and othertimes don't. If you want help, the burden is on you to explain what you are trying to do and how you get your numbers. It is clear that you haven't met that burden. And if this is for some kind of lottery scheme, then just don't bother. -- Regards, Tom Ogilvy "Frank Kabel" wrote in message ... Hi Michael sorry to say but this is the same example I didn't get in the first place. So it's probably me who did not see the logic behind it. Maybe someone else sees the algorithmn -- Regards Frank Kabel Frankfurt, Germany Hi ! Frank Kabel, I add 1 to 2010 because the last draw no in this example is 2010. I need a 1 as the first starting value,that's why I use the 2010-2010+1 to give me a value of 1. So if you take a look at the example below you will understand what I mean. e.g. Sheet 2 (Report) Col A===Col B A=== 1,-5,1,-2,2 ....... The value will add 1 to the current value if the next value of col A is the same. Thanks Michael. Frank Kabel wrote: *Hi I had a look at your post :-) But for example your first value is derived by the formula 2010-2010+1 why add '1' in this case. Your calculation just does not seem consistent for me (but that's probably just me). So you may explain this logic with some more detail. -- Regards Frank Kabel Frankfurt, Germany Hi! Frank Kabel, First of all I wish to thank you for trying to help me. I try to explain the logic as clear as possible and hopefully you can get the idea behind. In sheet 2, Col A contain the unrepeated value which can be found in col A of sheet 1. In sheet 2, col B values were derived from col B of sheet 1 appearing and skipping or in another word the gaps in between. Refer to my post, you can see them more clearly. Frank Kabel wrote: *Hi really not sure how you derived your values. Could you explain the logic behind your calculation? -- Regards Frank Kabel Frankfurt, Germany I need helps for the summary from sheet 1 to sheet 2 to be display horizontally so that I view them all at a glance. The macro will always execute from last row upward for the next 100 rows. Presently I use the auto filter method which cannot produce something as in sheet 2. Can it be done ? Kindly take a look at the example below. Sheet 1 (Datas) Rows===Col A 0001 A until 2000 A 2001 A 2002 C 2003 B 2004 A 2005 B 2006 B 2007 C 2008 C 2009 C 2010 A (e.g.last row) Sheet 2 (Report) Col A===Col B A 1,-5,1,-2,2 ....... B -4,2,-1,1 ......... C -1,3,-4,1,-2 ...... The value in col B read from Sheet 1 where Col A = Sheet 2 Col A from bottom up.It record the counting from last row and continue until skipping -100 rows of datas. e.g. the sheet 2 col B values were from row no 2010-2010+1=1, 2010-2005=-5,2005-2004=1,2004-2002=-2,2002-2000=2. The value in col B is positive when columns A of sheet 1 & sheet 2 are equal else the value will becomes negative. I hope I can make it clear for someone to help me. Thanks Michael --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ |
Summary display horizontally
Hi Tom
now I'm just glad what I'm not the only one who didn't see the logic pattern behind it. For the OP: As you see we're all really trying to understand what you want but please outline a specification to get some help. So just explain in 'pseudo code' how to derive your numbers. e.g. just tell us how you have calculated each of these numbers manually (all steps) -- Regards Frank Kabel Frankfurt, Germany Tom Ogilvy wrote: Michael, As I said to the first posting previous to this posting and it remains consistent, this is almost unintelligible. You talk about matching numbers on two sheets, but only show a listing for one sheet, then show some kind of mysterious result on the other. You sometimes add a 1 and othertimes don't. If you want help, the burden is on you to explain what you are trying to do and how you get your numbers. It is clear that you haven't met that burden. And if this is for some kind of lottery scheme, then just don't bother. |
Summary display horizontally
In plain speaking, I want the macro works like this:
Activate Sheet 1 Go to last row Stor 1 to counter Counter=counter+1 Look for individual value in col 1 of Sheet 1 and write to sheet 2 col & col 2 as above eample. Do until counter = 100 Thanks Michae -- Message posted from http://www.ExcelForum.com |
Summary display horizontally
Hi Michael
but this really does not match with your example. In you example you subtracted numbers and added 1 to a specific calculation (the first one) So this still does not make sense for me. Sorry to say :-( -- Regards Frank Kabel Frankfurt, Germany In plain speaking, I want the macro works like this: Activate Sheet 1 Go to last row Stor 1 to counter Counter=counter+1 Look for individual value in col 1 of Sheet 1 and write to sheet 2 col1 & col 2 as above eample. Do until counter = 100 Thanks Michael --- Message posted from http://www.ExcelForum.com/ |
Summary display horizontally
Hi! Frank Kabel,
Thanks for your patience. This is a very simplied way without talking the lapse of sales. Take a look at below: Sheet 1 A2000:A2010 contains A,B,B,A,A,C,C,C,A,B I want to display the report horizontally as below Sheet 2 A1=A B1=row no from bottom up, this gives 2,6,7,10 A2=B B2= 1,8,9 A3=C C3= 3,4,5 So you see there are no negative values in col 2 of sheet 2 I will do the negative value manually. Thanks Michael Frank Kabel wrote: *Hi Michael but this really does not match with your example. In you example you subtracted numbers and added 1 to a specific calculation (the first one) So this still does not make sense for me. Sorry to say :-( -- Regards Frank Kabel Frankfurt, Germany In plain speaking, I want the macro works like this: Activate Sheet 1 Go to last row Stor 1 to counter Counter=counter+1 Look for individual value in col 1 of Sheet 1 and write to sheet 2 col1 & col 2 as above eample. Do until counter = 100 Thanks Michael --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ |
Summary display horizontally
Hi Michael
but still how do you come to the values in column B: A1=A B1=row no from bottom up, this gives 2,6,7,10 A2=B B2= 1,8,9 A3=C C3= 3,4,5 I have one idea. Do you count the cell position starting with 1 in cell A2010 counting up. So the value 2,6,7,10 would represent: 2nd cell 6th cell 7th cell 10th cell always counting from the last row? Is this the way you want to calculate? -- Regards Frank Kabel Frankfurt, Germany Hi! Frank Kabel, Thanks for your patience. This is a very simplied way without talking the lapse of sales. Take a look at below: Sheet 1 A2000:A2010 contains A,B,B,A,A,C,C,C,A,B I want to display the report horizontally as below Sheet 2 A1=A B1=row no from bottom up, this gives 2,6,7,10 A2=B B2= 1,8,9 A3=C C3= 3,4,5 So you see there are no negative values in col 2 of sheet 2 I will do the negative value manually. Thanks Michael Frank Kabel wrote: *Hi Michael but this really does not match with your example. In you example you subtracted numbers and added 1 to a specific calculation (the first one) So this still does not make sense for me. Sorry to say :-( -- Regards Frank Kabel Frankfurt, Germany In plain speaking, I want the macro works like this: Activate Sheet 1 Go to last row Stor 1 to counter Counter=counter+1 Look for individual value in col 1 of Sheet 1 and write to sheet 2 col1 & col 2 as above eample. Do until counter = 100 Thanks Michael --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ |
Summary display horizontally
Hi Frank Kabel,
You are right I am always counting from the last row so that I ca track the last 100 sales and customer. If it is possible can the gap o individual customer be add in (I mean the negative value). You are really helpful and sorry for not being able to expres clearly. Thanks Michael Frank Kabel wrote: *Hi Michael but still how do you come to the values in column B: A1=A B1=row no from bottom up, this gives 2,6,7,10 A2=B B2= 1,8,9 A3=C C3= 3,4,5 I have one idea. Do you count the cell position starting with 1 i cell A2010 counting up. So the value 2,6,7,10 would represent: 2nd cell 6th cell 7th cell 10th cell always counting from the last row? Is this the way you want to calculate? -- Regards Frank Kabel Frankfurt, Germany Hi! Frank Kabel, Thanks for your patience. This is a very simplied way withou talking the lapse of sales. Take a look at below: Sheet 1 A2000:A2010 contains A,B,B,A,A,C,C,C,A,B I want to display the report horizontally as below Sheet 2 A1=A B1=row no from bottom up, this gives 2,6,7,10 A2=B B2= 1,8,9 A3=C C3= 3,4,5 So you see there are no negative values in col 2 of sheet 2 I will do the negative value manually. Thanks Michael Frank Kabel wrote: *Hi Michael but this really does not match with your example. In you exampl you subtracted numbers and added 1 to a specific calculation (th first one) So this still does not make sense for me. Sorry to say :-( -- Regards Frank Kabel Frankfurt, Germany In plain speaking, I want the macro works like this: Activate Sheet 1 Go to last row Stor 1 to counter Counter=counter+1 Look for individual value in col 1 of Sheet 1 and write to shee 2 col1 & col 2 as above eample. Do until counter = 100 Thanks Michael --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ -- Message posted from http://www.ExcelForum.com |
Summary display horizontally
Hi Michael
o.k. now we're getting somethere. Before giving you some formulas some questions first: 1. Do you really need these positions in a single cell/column ?. It would be much easier if you would put each number in a single cell! 2. What do you mean with b'gap' / negative numbers in this example. I yould suggest you provide the following: - post (in plain text - no attachment) a real live example of your first sheet (I doubt your customers are named 'A', 'B', etc.) - post teh exact expected result for this example on sheet 2 (please consider the suggestion to use a single cell for each result. so use several columns on sheet B) - explain the negative part - explain how many data you want to report - explain if you have a fixed number of rows in sheet 1 or if this can vary If you like you can also send me an example file privately and I'll have a look at it email: frank[dot]kabel[at]freenet[dot]de -- Regards Frank Kabel Frankfurt, Germany Hi Frank Kabel, You are right I am always counting from the last row so that I can track the last 100 sales and customer. If it is possible can the gap of individual customer be add in (I mean the negative value). You are really helpful and sorry for not being able to express clearly. Thanks Michael Frank Kabel wrote: *Hi Michael but still how do you come to the values in column B: A1=A B1=row no from bottom up, this gives 2,6,7,10 A2=B B2= 1,8,9 A3=C C3= 3,4,5 I have one idea. Do you count the cell position starting with 1 in cell A2010 counting up. So the value 2,6,7,10 would represent: 2nd cell 6th cell 7th cell 10th cell always counting from the last row? Is this the way you want to calculate? -- Regards Frank Kabel Frankfurt, Germany Hi! Frank Kabel, Thanks for your patience. This is a very simplied way without talking the lapse of sales. Take a look at below: Sheet 1 A2000:A2010 contains A,B,B,A,A,C,C,C,A,B I want to display the report horizontally as below Sheet 2 A1=A B1=row no from bottom up, this gives 2,6,7,10 A2=B B2= 1,8,9 A3=C C3= 3,4,5 So you see there are no negative values in col 2 of sheet 2 I will do the negative value manually. Thanks Michael Frank Kabel wrote: *Hi Michael but this really does not match with your example. In you example you subtracted numbers and added 1 to a specific calculation (the first one) So this still does not make sense for me. Sorry to say :-( -- Regards Frank Kabel Frankfurt, Germany In plain speaking, I want the macro works like this: Activate Sheet 1 Go to last row Stor 1 to counter Counter=counter+1 Look for individual value in col 1 of Sheet 1 and write to sheet 2 col1 & col 2 as above eample. Do until counter = 100 Thanks Michael --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ |
| All times are GMT +1. The time now is 02:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com