Excel Group & Merge Question

[Lowery]

I am by no means an Excel pro so maybe someone out there can help me.

I want to transform the table below from (a and b are columns)...
ab
13
14
15
26
27
28

to this by selecting each section and merging...the "-" are blank cells by the way.

ab
-3
14
-5
-6
27
-8

Now I know I can manually group and merge each section (in column a)....but the problem is my table is 30000 rows and it would take for ever. Can I do this automatically via a routine VBA or Macro or anything else?? I have searched all over the net but have found nothing.

[MarillionFan]

???

[Lowery]

yep it was hard to explain.....

I need to select all the "1" in column a....merge....select all the "2" ....merge.... and so on for 30000 rows....

[MarillionFan]

Can you not do this with a pivot table?

[Sockpuppet]

Edit: I see what you mean. Gimme 5 minutes.

Edit2: No I don't there is no logical way why the data is organised like that in the second table.

Are you saying you want it to go

04
06
07
11
12
13
25
26

to become

04
-6
-7
11
-2
-3
25
-6

[Lowery]

Exactly right ....

I see what you mean on not logical. After the merge has happened I want to align the text (in your example 0, 1 and 2) in the centre of the merged cell. That should be easy.

Tried a pivot table and also exporting to Excel from Access but still couldn't get there.

[MarillionFan]

=IF(A2=A1,TEXT(A2,"0"),TEXT(A2,"0")&TEXT(B2,"0"))

Start Data must be in row 2. Put formula in c2, drag down


A B C
1 1 11
1 2 1
1 3 1
2 5 25
2 6 2
3 4 34
3 6 3
3 7 3

[Sockpuppet]

Thats why MF gets paid the big bucks.

[MarillionFan]

Why he wants to do it god knows????

[Sockpuppet]

Why he wants to do it god knows????

His teacher set some homework?