look for a pipeID in a column and returns "broken" result

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Khoshravan - 04 May 2006 11:10 GMT

I have a column of pipeID (1400 IDs).
I have a column of Target pipes. (78pipe IDs)
I want a command to look Target pipes in pipeID and writes broken in front
of it if it finds in pipeID column and leave empty if it couldn't find. I
think I have to combine Hlookup and if command but I couldn't recognize how.
Will be happy if there is any help.

Signature

Rasoul Khoshravan Azar
Civil Engineer
Osaka, Japan

Reply to this Message

Pete_UK - 04 May 2006 12:56 GMT

Assume your pipeIDs are in column A from A2 to A1400. Assume your
Target pipes are in column C, beginning with C2. Enter this formula in
D2:

=IF(ISNA(VLOOKUP(C2,A$2:A$1400,1,0)),"","Broken "&C2)

Copy the formula down for as many items as you have in column C.

Hope this helps.

Pete

Reply to this Message

Khoshravan - 04 May 2006 14:14 GMT

Thanks a lot. That was a wonderfull solution. You saved me a lot of time. How
should I thank you?

Signature

Rasoul Khoshravan Azar
Civil Engineer
Osaka, Japan

> Assume your pipeIDs are in column A from A2 to A1400. Assume your
> Target pipes are in column C, beginning with C2. Enter this formula in
[quoted text clipped - 7 lines]
>
> Pete

Reply to this Message

Khoshravan - 05 May 2006 04:45 GMT

Dear Pete
Thank you very much for nice solution which moved me completely.
In the next step, I have to do this task for 50 earthquke scenarios, which
means in each earthquake scenario, around 100 pipes broken (Targer Pipes)
(number of broken pipes is not fixed in each scenario and differes from case
to case. For case one it was 78). Info of broken pipes for each scenario is
stored in one sheet. So I have 50 sheets, named run1 to run 50.
Maybe the best way is to write a MAcro.
I am not at wroting macro so I am looking for another easier way.
My problem is how to change run"i" sheet number when copying the formula you
mentioned, as it look to different sheets in each scenario (run1, run2,...
run50).
Woul be happy to get your opinion.
regards
rasoul

Signature

Rasoul Khoshravan Azar
Civil Engineer
Osaka, Japan

> Assume your pipeIDs are in column A from A2 to A1400. Assume your
> Target pipes are in column C, beginning with C2. Enter this formula in
[quoted text clipped - 7 lines]
>
> Pete

Reply to this Message

Pete_UK - 05 May 2006 09:55 GMT

I would suggest that you have your reference data (1400 pipes) in a
sheet called "data", and that you have the target pipe data in each of
your "run" sheets. That way your formula would always reference back to
one sheet, as follows:

=IF(ISNA(VLOOKUP(C2,'data'!A$2:A$1400,1,0)),"","Broken "&C2)

You could set up "run_x" sheet with this formula in D2 and use this as
a template to produce run1, run2, run3 sheets etc, just by CTRL-drag.
Then, when you add the target pipes to C2 downwards in each of the
"run" sheets, all you need to do is to select cell D2 and double-click
the fill-handle to copy the formula down for as many pipes as you have
in that run. (The fill-handle is the small black square in the bottom
right corner of the cursor).

Obviously, you may need to adjust references to C and D to suit your
sheet layout.

Hope this helps.

Pete

Reply to this Message