Use data from different cell

[JLeung]

Good day all,

I was directed here from ExcelIsFun from youtube, I have a problem which is the last problem im having at work. If you can help I would be extremely grateful!

Here it is:

Input rows look like this: 1,2,3,E,E,6,7
Current the output table looks like this: 0,0,0,1,1,0,0
(I'm using a lookup-function to count the 'E' values)

However the output rows should look like this: 0,0,0,3,3,0,0

In essence I want the "E" values to be replaced with the previous Columns Value.



Having read up from this forum I found this quote from the following thread

Try

Columns("A").Find(What:="Game", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues, lookat:=xlPart).Select

But i'm not sure how the code works so can't make use of it but i suspect it may be along the right lines.

I eagerly await a solution, and am grateful for all the help this forum has provided so far!

Many Thanks

Joe

 

[MikeWx]

Is it possible for the first value to be an E? If so, what should go in the first column?

 

[JLeung]

Good point Mike, the answer is no, there will never be a first value of E and E is the only character value. Thanks for raising this.

 

[MikeWx]

Are the numeric values always in ascending order from left to right, or can they be in random order?

 

[JLeung]

Yes they will always accend, its a tool to plot experience levels of staff, and the E denotes that they have been assigned to a diffent team so i would like their current experience levels on the "E team" to be recorded as their experience level justifies... also i am using a "countif" function currently NOT a lookup

 

[JLeung]

just realised my quote was from a vba question, was pretty tired when searching, ha oops

 

[MikeWx]

If the input data starts in Row 2 andis in columns A through G, the following formula copied in I2, then across to O2, will give you the output you are looking for:

=IF(ISNUMBER(A2),0,MAX($A$2:A2))

Copy columns I through O down for each input data row.

 

[JLeung]

thanks Mike that worked exactly how i wanted,

I found another method too, but it relies on there not being more than 4 E values and isnt as elegant

in cell F2

=IF(F1="E",IF(E1="E",IF(D1="E",IF(C1="E", B1, C1), D1), E1), F1)

Thanks a lot for you're help i'm implementing now

Joe

 

[MikeWx]

Thanks for your feedback.

Mike