0

I’m trying to create a VBA formula and not macro that picks the “ID’s” instead of “-” (dashes). The image below is an example of what I’m trying to do, for each row I want to pick the ID (number or text, written in cells where there aren't dashes).

a

In other words, I need a formula that picks ID from a selected row (i.e. [F17:P17]). The formula will delivery the value of the cell that doesn't contain “-”.

Example:

IF(logical_test;”pick a ID (number or text)”;”-”)

Unfortunately there’s not a formula that does the opposite job as IF, kind of IFNOT. IFNA doesn't help either.

Improve this question
2
  • Are you guaranteed that any non-dash from columns F to P will be a repetition? Or are you using G through P as the source and F as the answer?
    – rajah9
    Mar 25, 2013 at 16:42
  • Not sure what you are trying to achieve but there is a of doing IFNOT. Try: IF(NOT(test),true,false)
    – Dave Sexton
    Mar 25, 2013 at 16:48

2 Answers 2

Reset to default
3

In I17, put =CONCATENATE(C17,D17,E17,F17,G17,H17). This will give you something like ----1282525-.

Then in J17, put =SUBSTITUTE(I17,"-",""). This will remove the dashes, leaving you the single non-dash answer.

You may then fill down the formulas in these columns.

Improve this answer
1
  • 1
    Thanks rajah9, worked beautifully. I combined both formulas you gave me to be like: =SUBSTITUTE(CONCATENATE(G17;H17;I17;J17;K17;L17;M17;N17;O17;P17);"-";"")
    – Jacob Zucula
    Mar 25, 2013 at 16:56
0

This formula will give you the last value in the row that isn't a dash

=LOOKUP(2,1/(G17:P17<>"-"),G17:P17)

Improve this answer

You must log in to answer this question.