Summary display horizontally

[Michael168]

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

 

[Frank Kabel]

Hi
really not sure how you derived your values. Could you explain the
logic behind your calculation?

 

[Michael168]

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]

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.

 

[Michael168]

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]

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.

 

[Tom Ogilvy]

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.

 

[Frank Kabel]

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)

 

[Michael168]

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

 

[Frank Kabel]

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

 

[Michael168]

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]

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?

 

[Michael168]

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]

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