Consulting

Results 1 to 13 of 13

Thread: Table

  1. #1

    Matching values using formula

    Test.jpg
    Hello everyone i have this table and i need to get the values from left side and put into the right one. I have explaind everything into the table. i have tryed some excel formulas like lookup and some others but i dint finalize anything. If you help me ill appriciate that. Thanks in advance.
    Last edited by palata123; 05-23-2019 at 03:46 PM. Reason: Title of the post

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by palata123 View Post
    I have explaind everything into the table.
    Well, it's lost on me…

  3. #3
    Can i show you my formula?, becouse i have finished all my work and the formula works correctly but its a nested if formula and i cant repeat it over 7 times,,,so if you can help me to transform my formula into vba-code, ill appresciate that.
    regards pascal!

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Yes, but put it into a workbook and attach that here.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5

    Transform formula into vba-code.

    I have atached the workbook here, and the formula. The work is done but as long as the nested IF formula is up to 7 times and my range is bigger than this i need a loop through this to complete my range. The formula takes the cell with values make the diff between these two values and if the 2 conditions are meet the values are copied in that range to the specified cell(J3, K3, L3) in L3 is the value in the second row that belongs to the column where the value is finded.
    Attached Files Attached Files

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    In the attached, formulae that may work for you, I'm not sure, are in the green shaded cells N3:P3.
    The three formulae are all array-entered as before.
    They can be copied down.

    If, instead of a table from columns B to I, you have a table in columns B to M, the formula in cell N3 (but obviously in another cell!) changes from:
    =INDEX($B3:$I3,MIN(IF(((CHOOSE({1,2,3,4},B3,D3,F3,H3)<>0)+(CHOOSE({1,2,3,4},C3,E3,G3,I3)<>0)=2)*(ABS(CHOOSE({1,2,3,4},B3,D3,F3,H3)-CHOOSE({1,2,3,4},C3,E3,G3,I3))<=0.1),{1,3,5,7})))
    to:
    =INDEX($B3:$M3,MIN(IF(((CHOOSE({1,2,3,4,5,6},B3,D3,F3,H3,J3,L3)<>0)+(CHOOSE({1,2,3,4,5,6},C3,E3,G3,I3,K3,M3)<>0)=2)*(ABS(CHOOSE({1,2,3,4,5,6},B3,D3,F3,H3,J3,L3)-CHOOSE({1,2,3,4,5,6},C3,E3,G3,I3,K3,M3))<=0.1),{1,3,5,7,9,11})))
    There's an area of cells S2:V7 which is how the formulae were derived. Pay attention to column S formulae. The formulae in columns T:V are very simple, and perhaps having a separate helper column like column S (which can be hidden) might be a way you want to go.
    All these cells can be deleted.

    I've probably over-complicated the formula and I feel there must be a much simpler formula!
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    At first i should thank you a lot for helping me. I have seen both of tables that works great as i needed, but the first with the green color selected have a problem when the conditions arent meet and it takes the first value in the row, the second one with colums S is ok. So i have to add to the formula all columns i got and im afraid of its length. If i want to try another way to use match and index but this time to specify the first number to be >=1.8 and <=1.9 and the rest will be the same. Im working on that and till now im here: =INDEX(B3:I3,MATCH(TRUE,INDEX((B3:I3>=1.8),0),0)) but i dont not how to add the second condition for B3:I3<=1.9,,,the first one works and find the first value with that conditions but if its a number value 4.00 it find that first becouse i have to put the second condit. What you think about this? One more time thanks a lot for your support.
    Best Regards.

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by palata123 View Post
    all columns i got and im afraid of its length
    How many columns actually?

    Quote Originally Posted by palata123 View Post
    but as long as the nested IF formula is up to 7 times
    In Excel 2007 onwards you can nest 64 IFs, not that I'd recommend it.

    While I can reverse engineer a formula you have written and understand what it's doing, when that formula doesn't quite work as you want, things get difficult for me.
    I will write a formula for you (using macro code if necessary to create a user-defined function) I do need to understand what you're tryinmg to do.
    So, could you explain in words (not formulae) what you're trying to do? It might also be helpful if you were to explain the context, that is what these numbers represent in the real world and what/why you're trying to extract from them.

    Quote Originally Posted by palata123 View Post
    when the conditions arent meet and it takes the first value in the row
    I can fix this.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9

    Transform formula into vba-code.

    I have atached a real copy of the workbook and i have described in the end what values i need to match.
    Attached Files Attached Files

  10. #10
    p45cal i found this one after searching around, take a look at this:
    =INDEX(A2:GR2,MATCH(1,INDEX((A2:GR2>=1.8)*(A2:GR2<=1.9),0),0))
    Here the 2 conditions are meet and returns that desired value, if want take a try.

  11. #11
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Your formula in your last message simply looks for the first value in the row with a value between (and including) 1.8 and 1.9. This is quite different from your original requirement.

    Going back to your requirements in cell HB2:

    Was I wrong to assume you were comparing pairs of columns; comparing A with B, then C with D etc. (and NOT, for example, comparing B with C)?
    Take a look at your formula result in cell GS9. It's found the 1.9 at cell DF9 which is the 2nd column of a pair (I altered the value to trick your formula, but are you sure this can't happen in real life?).

    Look also at cell GS10, it found the 1.9 at cell DE10 (also altererd by me); the adjacent value is 2.29, a much greater difference than 0.1.

    Take a look at row 3 too.

    The attached has:
    - your formula (from msg#10) in column GS with variants of it in columns GR and GT.
    - my indexing formula in column GW with simple formulae in columns GX:GZ.
    - yellow conditional formatting to highlight the cells found by column GW.
    - manual orange formatting to highlight differences.

    If you want, I'll still write code and/or a user-defined function, but I need the exact specifications (pairs of columns or not?, do we have to separate columns A:CV from CW:GR? is the 1.8-1.9 range what you're looking for or a difference of less that 0.1 etc.)
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  12. #12
    Good evening p45cal. Whats up to you, i hope your fine. At first i would like to give a big Thank to you for supporting me. Yes the point was to compare just column A&B, C&D and NOT B with C. I see everything how the formula works with index GW and simple formula in GX GY and GZ, thats exatly what i wanted while the formula in GS,GT and GU i did with that simple formula i tell to you at msg#10 but some time at GU if you see it takes wrong the first value with 2(+5.5) while in GU must be only 1(+-x.x). Im analyzing a little bit the complexity of the formula in GW to understand how it works ABS and ISODD/ISEVEN are not familiary to me so i have a lot to learn with excel-Formula. You did a great job and im very thankfull to you. You are a MASTER!

  13. #13
    Working with that workbook i just realised another formula to match all values in columnsA and columsB or other columns using a pre-value inside that column. Im puting the formula here anyway someone in time will need that:
    {=INDEX($AB$2:$AB$20,MATCH($AB$1="1 (-4.5)",INDEX(($AB2:$AB$22>=0),0),0))} this formula puts the value from range (AB2:AB22) only if the first value in AB1="1(-4.5)" or whatever in the desired place you put the formula. If i have something wrong correct me.

Tags for this Thread

Posting Permissions

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