PDA

View Full Version : [SOLVED:] Search left from specific column eg. "y"



nugol
02-18-2016, 05:25 AM
Hy guys,

I got a little problem and would appreciate some help :)

In my list, I want to find all rows where the current letter (status) is x and write a 1 right beside it.
The whole thing should start from Column F and go left through the neighbouring cells. In my example below, there would be a 1 in Column F Row 1 and Row 4.

In short, the macro should go left from Column F and should find out if the current status is x or xx and make it 1 in Column F. Else it should just write a 0.

Here now my litte example:



A

B

C

D

E

F



Row1

r

r

r

r

x

1



Row2

r

r

xx

x

r




Row3

r

r







Row4

r

r

x



1



Row5 r x xx 1

Thanks in advance guys. :)

mikerickson
02-18-2016, 06:57 AM
In F1 you could put the formula =IF(MATCH("zzzzzz",A1:E1)="x", 1, 0)

nugol
02-18-2016, 07:09 AM
Hy thanks, for your reply. Not sure what you mean with "zzzzz"?

mikerickson
02-18-2016, 07:27 AM
"zzzzzz" is just a very large string if there is text in A1, its very likey that A1<"zzzzzzz".
Similar to the number 9E+99


And I mean that you should use that in the formula.

mikerickson
02-18-2016, 07:30 AM
And I just realize that I used the right consept but the wrong formula


Try =IF(LOOKUP("zzzzzz", A1:E1)="x", 1, 0)

nugol
02-18-2016, 07:33 AM
We posted the same time, so yeah I tried Vlookup. That is working for sure, but the problem remains.

I never know how many columns are filled with x, if there is an x or where there is the x. So I cannot really use Vlookup? since it has
to focus on one column.

That's why I wanted to go left from column F because that would be dynamic and some years ago such thing worked the other way round.
Shame I can't find that anymore.

mikerickson
02-18-2016, 07:43 AM
That formula uses LOOKUP not VLOOKUP.

nugol
02-18-2016, 07:49 AM
My bad, yeah now it works almost as intended. thx so far :)

The only thing that remains is that there also has to be a 1 if there are xx (two x) in the row. they should be handled the same as one x.

Edited it in the first comment to make it more clear.

mikerickson
02-18-2016, 07:53 AM
=IF(OR(LOOKUP("zzzzzz", A1:E1)="x", LOOKUP("zzzzzz", A1:E1)="xx"), 1, 0)


When you say "in the row", do you mean "in the row" or "in the last cell of the row"?
Is the result from Row 2 of the OP supposed to be 1 or 0?

nugol
02-18-2016, 07:57 AM
THanks for your support. Seems to work now! :)