need some help

[bam12]

need some help on this

in column e a cell will contain either a "P" or "V" if it is a "P" i would like it to take the contents of column a and b in same row as the cell with "P" in it and move the contents of column cells A and B to I and J and continue this moving down a row each time it find cell in column e with a p

comma below is column break

example
a7 is the date field
1/23/06,8,,V,,,,,
2/3/06,8,,P,,,2/3/06,8 problem being this will actually start on I7 and j7
and each time it find a p will be the next line down so when it finds next p it will put that in I8 and J8


Thnaks in advance for any help

 

[Peter_SSs]

bam12

This is not entirely clear to me so a few questions:
1. Can you confirm that columns I and J will not have blank rows in its data?
2. Will the values be deleted from columns A and B when moved to columns I & J?

Is this the sort of thing you want to end up with?

Mr Excel.xls
	A	B	C	D	E	F	G	H	I	J	K
6
7	05/08/2006	8			P				05/08/2006	8
8	10/08/2006	6			P				10/08/2006	6
9	15/08/2006	5			V				20/08/2006	8
10	20/08/2006	8			P				04/09/2006	9
11	25/08/2006	6			V
12	30/08/2006	4			V
13	04/09/2006	9			P
14	09/09/2006	6			V
15
Move Data

 

[bam12]

yes

yes column I and J will have no data or formulas in them

No the info has to stay in column A ND B as well

And yes that is exactly how i want it ot look

thanks

 

[bam12]

add

column A and B will also be added to at times, so when this happens the new lines will be added to I and J

 

[Peter_SSs]

bam12

I'm sort of feeling that a VBA approach might be good here. However, as that is not my strong point here is an attempt at a formula approach.

1. I've entered the letter of interest in I6.
2. Formula in I7 (copied across and down): =IF(ROWS(I$7:I7)>COUNTIF($E$7:$E$16,$I$6),"",INDEX(A$7:A$16,SUMPRODUCT(SMALL(--($E$7:$E$16=$I$6)*(ROW(A$7:A$16)-ROW(I$6)),COUNTIF($E$7:$E$16,"<>"&$I$6)+ROWS(I$7:I7)))))
3. You may need to format column I as Date and column J as General or Number

Mr Excel.xls
	A	B	C	D	E	F	G	H	I	J	K
6									P
7	05/08/2006	8			P				05/08/2006	8
8	10/08/2006	6			P				10/08/2006	6
9	15/08/2006	5			V				20/08/2006	8
10	20/08/2006	8			P				04/09/2006	9
11	25/08/2006	6			V
12	30/08/2006	4			V
13	04/09/2006	9			P
14	09/09/2006	6			V
15
Move Data

 

[bam12]

thanks

Peter

Thanks it works like a dream.....just wish i knew a little more about formulas so i would have to always ask for help

but i am learning

 

[bam12]

small problem

i have this formula in another cell that is checking column I, and now it is not working right, is this because of the formula in that column


=SUMPRODUCT((I$7:I$33>TODAY()-90)*1)

what this does is keep track of numbers of days that are less then 90 before todays date

 

[Peter_SSs]

bam12

See if this works for you:
=COUNTIF(I$7:I$33,">"&TODAY()-90)

 

[bam12]

worked

Peter

it worked many thanks

Paul

 

[UPN]

describe

could someone explain to me , Peters formula.. i am just trying to figure out how it works

this i formula is it also back in prior messages

=IF(ROWS(I$7:I7)>COUNTIF($E$7:$E$16,$I$6),"",INDEX(A$7:A$16,SUMPRODUCT(SMALL(--($E$7:$E$16=$I$6)*(ROW(A$7:A$16)-ROW(I$6)),COUNTIF($E$7:$E$16,"<>"&$I$6)+ROWS(I$7:I7)))))