Closed Thread
Results 1 to 13 of 13

lookup data by splitting 'binary' value

  1. 04-24-2013, 12:30 PM #1
    Zestje
    Zestje is offline
    Registered User
    Join Date
    04-19-2013
    Location
    Brunssum
    MS-Off Ver
    Excel 2003
    Posts
    17

    Question lookup data by splitting 'binary' value

    Hello,

    I have a table like the following:
    Example:

    INDEX DATA
    1.....A
    2.....B
    4.....C
    8.....D
    16.....E
    32.....F
    64.....G
    128.....H

    The counting is just like binary. I want to fill a cell with data by looking up a value from a cell, 'splitting' it according to the table and filling another cell by adding the looked up values as text.
    So the value 3 corresponds to 'A B' (1 + 2). The value 57 corresponds with 'A D E F' (1 + 8 + 16 + 32).
    I have not been able to find how to do this since I have no clue what this kind of function is called in excel and haven't been able to find the using a search.
    Many thanks in advance for any help!
    Reply With QuoteRegister To Reply
  2. 04-24-2013, 12:32 PM #2
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: lookup data by splitting 'binary' value

    For example, ...?
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  3. 04-24-2013, 01:19 PM #3
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: lookup data by splitting 'binary' value

    I couldn't come up with a way without using helper cells.
    The final step would be to combine the helper cells with something like this
    =TRIM(E12&" "&E11&" "&E10&" "&E9&" "&E8&" "&E7&" "&E6&" "&E5&" "&E4&" "&E3)

    I came up with a few ways to pull out the appropriate codes.
    With your value in D1
    First method:
    In one column copied down (starting in D3 for this example)
    =IFERROR(INDEX($A$1:$A$8,MATCH($D$1-SUM($D$2:D2),$A$1:$A$8)),"")
    Next to it, to pull out the Letters
    =IFERROR(VLOOKUP(D3,$A$1:$B$8,2),"")

    Second method using DEC2BIN to convert to binary
    In G2
    =DEC2BIN($D$1)
    In G3
    =IF(ROW(A1)<=LEN($G$2),IF(MID($G$2,LEN($G$2)-ROW(A1)+1,1)="1", INDEX($B$1:$B$8, ROW(A1)),""),"")
    copied down

    Third method, simply substituting DEC2BIN($D$1) for $G$2 in method 2
    =IF(ROW(A1)<=LEN(DEC2BIN($D$1)),IF(MID(DEC2BIN($D$1),LEN(DEC2BIN($D$1))-ROW(A1)+1,1)="1", INDEX($B$1:$B$8, ROW(A1)),""),"")
    See attachment
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  4. 04-24-2013, 03:09 PM #4
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: lookup data by splitting 'binary' value

    Not thinking very clearly today. For method 1, instead of two columns just use this

    =IFERROR(INDEX($B$1:$B$8,MATCH($D$1-SUM($D$2:D2),$A$1:$A$8)),"")
    Reply With QuoteRegister To Reply
  5. 04-24-2013, 03:21 PM #5
    Zestje
    Zestje is offline
    Registered User
    Join Date
    04-19-2013
    Location
    Brunssum
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: lookup data by splitting 'binary' value

    Thank you very much for your help!
    Could you please also post the Excel file as a 2003 .xls file? At home I don't have the 2007 or 2010 version :-(
    Reply With QuoteRegister To Reply
  6. 04-24-2013, 03:29 PM #6
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: lookup data by splitting 'binary' value

    Okay, had to replace the ISERROR function as it is not available in 2003. There is a 2007+ and a 2003 sheet included in this workbook.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 04-24-2013, 03:47 PM #7
    Zestje
    Zestje is offline
    Registered User
    Join Date
    04-19-2013
    Location
    Brunssum
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: lookup data by splitting 'binary' value

    Fantastic! Thank you very much!
    Reply With QuoteRegister To Reply
  8. 04-25-2013, 09:53 AM #8
    Zestje
    Zestje is offline
    Registered User
    Join Date
    04-19-2013
    Location
    Brunssum
    MS-Off Ver
    Excel 2003
    Posts
    17

    Question Re: lookup data by splitting 'binary' value

    I'm sorry but I have another question:

    How do I implement this system to automatically fill another table with input values?
    E.G. the table's first column looks like this:


    1
    45
    17
    23
    16
    4
    13


    The second column should automatically be filled by the corresponding looked up values as text:

    1 A
    45 A C D F
    17 A E
    23 A B C E
    16 E
    4 C
    13 A C D

    Maybe as a VBA function?
    P.S. I'm sorry for possibly asking too simple questions, but have only very little knowledge beyond the standard Excel functions and almost no experience witb VBA at all
    Reply With QuoteRegister To Reply
  9. 04-25-2013, 10:57 AM #9
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: lookup data by splitting 'binary' value

    Maybe a VBA would be less labor intensive but I am just a novice with VBA so here's the Formula Solution

    With values in A2:A8 (see attachment) and table of relative codes in E2:F9

    In G2 copied down and across

    =IF(ROW(A1)<=LEN(DEC2BIN(INDEX($A$2:$A$8,COLUMN(A1)))),IF(MID(DEC2BIN(INDEX($A$2:$A$8,COLUMN(A1))),LEN(DEC2BIN(INDEX($A$2:$A$8,COLUMN(A1))))-ROW(A1)+1,1)="1", INDEX($F$2:$F$9, ROW(A1)),""),"")

    In B2 copied down

    =TRIM(INDEX($G$9:$N$9,ROW(A1))&" "&INDEX($G$8:$N$8, ROW(A1))&" "& INDEX($G$7:$N$7, ROW(A1))&" "&INDEX($G$6:$N$6, ROW(A1))&" "&INDEX($G$5:$N$5, ROW(A1))&" "&INDEX($G$4:$N$4, ROW(A1)) &" "& INDEX($G$3:$N$3, ROW(A1))&" "& INDEX($G$2:$N$2, ROW(A1)))
    Questions?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 04-26-2013, 03:03 PM #10
    Zestje
    Zestje is offline
    Registered User
    Join Date
    04-19-2013
    Location
    Brunssum
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: lookup data by splitting 'binary' value

    I'm slowly losing my brain. I've been trying for almost a day now to get this to work the way I want it , but just can't get it right.
    I have to do all the calculations on one row to be able to use it in my table, but can't manage to 'transpose' the formulae without errors.
    At one point I thought it was working but then I noticed that due to a DEC2BIN limitation it didn't work for large numbers .

    Please see the attached file for an example of what I would like to have.
    I hope someone can help me out here...again...

    Example.xls
    Reply With QuoteRegister To Reply
  11. 04-26-2013, 03:33 PM #11
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: lookup data by splitting 'binary' value

    7000 rows is probably a bit much to do with the type of formulas I was giving you. I'll put in a call for a VBA expert. It should be a relatively short routine.
    Reply With QuoteRegister To Reply
  12. 04-27-2013, 03:35 AM #12
    Zestje
    Zestje is offline
    Registered User
    Join Date
    04-19-2013
    Location
    Brunssum
    MS-Off Ver
    Excel 2003
    Posts
    17

    Exclamation Re: lookup data by splitting 'binary' value

    Thank you very much again, I really appreciate all the help!
    Reply With QuoteRegister To Reply
  13. 04-27-2013, 06:18 PM #13
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: lookup data by splitting 'binary' value

    This thread is closed in light of movement to VBA forum for another try.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
Closed Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1