Excel: re-organize data

[cheer8923]

I've given a spreadsheet looks like this:

10/6/2008
BM 24
F 5
BM + F 29

10/7/2008
BM 20
F 3
BM + F 23

2 columns. Is it possible to write sth in Excel to re-org the data into row based:

Date BM F BM+F
10/6 24 5 29
10/7 20 3 23

Thanks!

 

[SkipVought]

Hi,

Is there any way to find the source data that was used to create the report in the first place?

Short of that,

This assumes that your data starts in A2 with two columns as in your example, with a repeating 5 rows WITHOUT FAIL!

Your headings in ROW 1 columns D:G
Date BM F BM+F

In cell...
[tt]
C2: =MOD(ROW()-2,5)
D2: =IF(MOD(ROW()-2,5)=COLUMN()-4,$A2,D1)
E2: =IF(MOD(ROW()-2,5)=COLUMN()-4,$B2,E3)
[/tt]
copy E2 across

copy C2:G2 down thru all rows of data

AutoFilter on column C, Criteria = 0

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cheer8923]

I did not quite follow. The result is sth like 2 blank 0

 

[Gavona]

It is interesting to see the different ways a task can be achieved.

You haven't really explained out your problem sufficiently. Tell us explicitly the data in the relevant cells, the formula you have used and the result it gives. "Something like" is not enough for us to help you help yourself.

Skip's solution works for me and assumes that:
A2=10/6/2008 B2=24
A3=BM
A4=F
A5=BM+F
A6
A7=10/7/2008

Simply copy and paste Skip's formulae into the cells he indicated. Result:
C2=0
C3=1
C4=2
C5=3
C6=4
C7=0
C8=1
etc.

Column D should be formatted as dates.
Personally, I find it easier to follow how it works if you modify the formula Skip has suggested for columns D and E to:
D2: =IF($C2=COLUMN()-4,$A2,D1)
E2: =IF($C2=COLUMN()-4,$B2,E3)

An alternate solution would test for a numeric value in column A (dates are numbers). In case you are interested:
C2:=ISNUMBER(A2)
D2:=IF($C2,A2,"")
E2:=IF($C2,B3,"")
F2:=IF($C2,B4,"")
G2:=IF($C2,B5,"")

As with Skip's copy to values, filter column C and delete the rows you no longer need.

Gavin

 

[cheer8923]

I made mistake in the previous. Here is what I got after copying the formula and fill to right and then down:

[tt]
A B C Date BM F BM+F
10/2/08 0 10/2/08 24 5 29
BM 24 1 10/2/08 24 5 29
F 5 2 10/2/08 3 5 29
BM+F 29 3 10/2/08 3 23 29
10/3/08 4 10/2/08 3 23 0
BM 20 0 BM 3 23 0
F 3 1 BM 3 23 0
BM+F 23 2 BM 0 23 0
[/tt]

Beyond this, if the spacing is irregular, but the date in column A can signal the start of a new section, is there any solution?

 

[SkipVought]

"I've given a spreadsheet looks like this:"

"This assumes that your data starts in A2 with two columns as in your example, with a repeating 5 rows WITHOUT FAIL!"

"Beyond this, if the spacing is irregular, but the date in column A can signal the start of a new section, is there any solution?"

This is why a detailed, complete and accurate description of your data is a vital importance, if you expect to get a solution that will work! You were NOT given a spreadsheet that looks like you first posted. Only PART of the spreadsheet looks that that. And now you have an example that looks DIFFERENT!

Please state your problem again, clearly, concisely and completely. Ananlyze ALL of your data first to be absolutely sure that your description is accurate and complete.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

"Beyond this, if the spacing is irregular, but the date in column A can signal the start of a new section, is there any solution?"

Well you could read my post!

An alternate solution would test for a numeric value in column A (dates are numbers)............

Gavin

 

[SkipVought]

Using Gavin's suggestion, in Column C, test for a NUMBER. If TRUE then 0 otherwise the previous cell's value + 1 and you would get in Col C...
0
1
2
etc

Then base the formulas in the subsequent columns D and over, based on the value in col C, rather than the MOD function in the formula.

SIMPLE!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cheer8923]

Here is what I want:

[tt]
A B
10/2/08 10
BM 24
F 5
BM+F 29
10/3/08 7
BM 20
F 3
BM+F 23

to the below:

Date Count BM F BM+F
10/2/08 10 24 5 29
10/2/08 7 20 3 23
[/tt]

There could be blank rows before the date.

 

[SkipVought]

You have the solution already! Did you try testing for DATE as was suggested?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cheer8923]

I see what you mean. I am not good at excel so it will take me some time to implement. However, is there any way to get rid of the duplicates?

 

[Gavona]

Try the solution. My first post included at the end a complete working set of formulae based on your original description of the challenge. So I don't really see why it will "take some time to implement".
Your latest description adds a "Count" column to the original requirement but if you implement the solution as I first posted (the end of my post) then I am SURE you can insert a further column and put the necessary formula into it.

What duplicates?

copy to values, filter column C and delete the rows you no longer need.

Re-read my first post, including the last line. If you are still having problems then post with an example of the duplicates. Similarly if you don't understand the suggestions made (eg how to use Autofilter to delete rows you don't want) or how a particular bit of the solution works then post back BUT make sure you look at help first.

Gavin