• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Shortest route

P

Pofski

Member
Hi,

i was trying to achieve the following.
In my attached file, i have 23 columns.
Each column has either a number, or is empty. each column also refers to a separate table (not included in file)
If there is a number in a column, then i would have to extract something next to the ID of 1 of the tables, with the end result beeing that all the id numbers should be looked up in a table that has information about it.

for example: Id 64814 is found in all the tables, so looking it up in table 1 would do.
id 17922 is only in table 2 and 6, so would have to be looked up in either one.

Now i am trying to find a way to do this in the least amount of steps.
i hope this explanation makes sense.

Thanks in advance.

Sincerely,

Pofski
 

Attachments

  • BestCombi.xlsx
    66.7 KB · Views: 17
Hi Pofski ,

An interesting problem.

I think if we use a helper column with the formula :

=COUNTBLANK(B2:X2)

copying it down , and then sorting the entire data range on this column , you will be able to see how you select the tables.

See if this helps.

Narayan
 

Attachments

  • BestCombi.xlsx
    72.8 KB · Views: 7
Hi, pofski!
Use Y as a helper column and enter this array formula to retrieve the related table from the header:
Y2: =SI.ERROR(INDICE(B$1:X$1;COINCIDIR(VERDADERO;LARGO(B2:X2)<>0;0));"") -----> in english: =IFERROR(INDEX(B$1:X$1,MATCH(TRUE,LEN(B2:X2)<>0,0)),"")
Or this if just the table no.:
Y2: =SI.ERROR(COINCIDIR(VERDADERO;LARGO(B2:X2)<>0;0);"") -----> in english: =IFERROR(MATCH(TRUE,LEN(B2:X2)<>0,0),"")
Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.
Just advise if any issue.
Regards!
 
Thank you for the help Sir and Narayan.
Your formula works, but could you please explain me how? I seem to have difficulty's understanding array formulas, and although it seems like a simple formula, i don't see how it works.

Thanks
 
Hi, Pofski!

You're lucky I didn't post a solution find at Microsoft website, by an MVP:
http://answers.microsoft.com/en-us/...ell-in-a/f697b9f1-1fc9-49f1-8ee4-9f4c4c1cc618

Let me take the short version, the long is just an INDEX:
=IFERROR(MATCH(TRUE,LEN(B2:X2)<>0,0),"")

The embracing IFERROR is just for avoid displaying error #N/A if nothing found, so this is left:
=MATCH(TRUE,LEN(B2:X2)<>0,0)

Suppose you have this values in the related range: empty until column J, A in J2, B in K2, empty, empty C in N2, empty until column X.

If you debug (F2 to enter into edit mode, then select B2:X2, then press F9... and finally press Escape so as to keep the original formula) you'll get this array tied to the range:
B2:X2
i.e.,
{0\0\0\0\0\0\0\0\"A"\"B"\0\0\"C"\0\0\0\0\0\0\0\0\0\0}

If you repeat the procedure including the LEN function, you'll get this:
LEN(B2:X2) = LEN({0\0\0\0\0\0\0\0\"A"\"B"\0\0\"C"\0\0\0\0\0\0\0\0\0\0})
i.e.,
{0\0\0\0\0\0\0\0\1\1\0\0\1\0\0\0\0\0\0\0\0\0\0}

Now including the comparison for not zero, you'll get this:
LEN(B2:X2)<>0 = {0\0\0\0\0\0\0\0\1\1\0\0\1\0\0\0\0\0\0\0\0\0\0}<>0
i.e.,
{FALSE\FALSE\FALSE\FALSE\FALSE\FALSE\FALSE\FALSE\TRUE\TRUE\FALSE\FALSE\TRUE\FALSE\FALSE\FALSE\FALSE\FALSE\FALSE\FALSE\FALSE\FALSE\FALSE}

And on that array is where the MATCH for TRUE is issued:
MATCH(TRUE,LEN(B2:X2)<>0,0)
i.e.,
{FALSE\FALSE\FALSE\FALSE\FALSE\FALSE\FALSE\FALSE\TRUE\TRUE\FALSE\FALSE\TRUE\FALSE\FALSE\FALSE\FALSE\FALSE\FALSE\FALSE\FALSE\FALSE\FALSE}

Which returns a 9.

You may be wondering "ok, but where does the array concept plays here?". Well, MATCH function has this structure:
MATCH(argument,range,search_type)
so if you knew the 1st non-empty value (A), you should have written this normal not-array formula:
=MATCH("A",B2:X2,0)
to get the 9.
But as you don't know the value as you want any non-empty value, and as MATCH doesn't accept wildcards, you wouldn't have used this:
=MATCH("*",B2:X2,0)
or:
=MATCH("?",B2:X2,0)
which seems to happen to work! But only because the 1st value is text, if it were a number or a string, it wouldn't have been found. Try entering 12 in I2 and then 01/01/2013 in H2, you'll still be getting a 9.

So if wondering "why can't I use the simple =MATCH(TRUE,LEN(B2:X2)<>0,O) as a normal formula? The answer is in the function structure: it requires that the 2nd argument be a range (or an array) and in this case it'd be a boolean FALSE returned by the:
LEN(B2:X2)<>0
i.e.,
{0\0\0\0\0\0\0\0\1\1\0\0\1\0\0\0\0\0\0\0\0\0\0}

The workaround is to build the 0/1 array introducing the array formula concept to construct the range, and then all happy, starting for the MATCH function.

Hope it helps.

Regards!
 
You must log in or register to reply here.
Back
Top