+ Reply to Thread
Results 1 to 11 of 11

Comparing a Table with another data Table to replace with a Code

  1. 04-26-2008, 12:22 PM #1
    acsishere is offline
    Registered User
    Join Date
    04-15-2008
    Location
    Tamil Nadu, India.
    MS-Off Ver
    Microsoft Office 2016
    Posts
    584

    Arrow Comparing a Table with another data Table to replace with a Code

    Hi, Best wishes!

    I have attached a file which explains my problem.

    It is that a table is to be compared with another table (which consists a lots of data) and need to be replaced with a code.

    I feel that a macro only will do.

    I appreciate any help. Thanks in advance.

    acsishere.
    Reply With QuoteRegister To Reply
  2. 04-26-2008, 12:41 PM #2
    mrice is offline
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967
    You haven't attached the file
    Martin
    Reply With QuoteRegister To Reply
  3. 04-26-2008, 12:44 PM #3
    acsishere is offline
    Registered User
    Join Date
    04-15-2008
    Location
    Tamil Nadu, India.
    MS-Off Ver
    Microsoft Office 2016
    Posts
    584

    Arrow Dear Mr. MRICE

    Yes! No excuse. I'm sorry.

    Now it is uploaded. Please try to give me a solution.

    Pl.

    acsishere.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 04-26-2008, 01:06 PM #4
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    How about an array formula solution:

    =INDEX($A$6:$A$10, MATCH(N2 & Q2 & T2 & W2, $B$6:$B$10 & $C$6:$C$10 & $D$6:$D$10 & $E$6:$E$10, 0))

    Enter the formula in Z2. It MUST be confirmed with Ctrl+Shift+Enter, not just Enter. You'll know if you did it right if curly braces appear around the formula, and you cannot enter these by typing.

    Then just drag the formula down.
    Reply With QuoteRegister To Reply
  5. 04-26-2008, 01:42 PM #5
    acsishere is offline
    Registered User
    Join Date
    04-15-2008
    Location
    Tamil Nadu, India.
    MS-Off Ver
    Microsoft Office 2016
    Posts
    584

    Arrow Dear Mr. SHG

    Thanks for your kind solution, in no time.

    It is nicely working. But, If I changed the GROUP TABLE order as mentioned below it gives #NA error:

    (As mentioned in the previously attached file) for example:
    group table code / sub1 / sub2 / sub3 / sub4
    originally 1 / 41 / 42 / 43 / 48

    when changed like 1 / 42 / 42 / 41 / 48

    (when the sub1 & sub3 codes are changed or rather interchanged, it gives error).

    Please give me a solution. If it could be a VBA macro, it will be a nice one.

    acsishere.
    Last edited by acsishere; 04-26-2008 at 01:51 PM.
    Reply With QuoteRegister To Reply
  6. 04-26-2008, 03:26 PM #6
    acsishere is offline
    Registered User
    Join Date
    04-15-2008
    Location
    Tamil Nadu, India.
    MS-Off Ver
    Microsoft Office 2016
    Posts
    584

    Question Hi Friends,

    Any solution for the above?

    Please help me out to to solve this as this affects further processes.

    Thanks in advance.

    acsishere.
    Reply With QuoteRegister To Reply
  7. 04-26-2008, 10:39 PM #7
    acsishere is offline
    Registered User
    Join Date
    04-15-2008
    Location
    Tamil Nadu, India.
    MS-Off Ver
    Microsoft Office 2016
    Posts
    584

    Question Hi Friends,

    Please give me a solution for the above query.

    I already attached a file in which I explained my problem.

    Any help will be appreciated.

    Thanks in advance.

    acsishere.
    Reply With QuoteRegister To Reply
  8. 04-27-2008, 10:12 AM #8
    acsishere is offline
    Registered User
    Join Date
    04-15-2008
    Location
    Tamil Nadu, India.
    MS-Off Ver
    Microsoft Office 2016
    Posts
    584

    Question Dear Gurus,

    Can any one please give any solution?

    Please...

    acsishere.
    Reply With QuoteRegister To Reply
  9. 04-27-2008, 11:55 AM #9
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    This code checks for a match by checking the sum of the columns in the main table with the sums of the code table.
    Please Login or Register  to view this content.
    There is no need to use large text, bold etc in posts to attract attention.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example
    Reply With QuoteRegister To Reply
  10. 04-27-2008, 03:22 PM #10
    Teethless mama is offline
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211
    Quote Originally Posted by acsishere
    Yes! No excuse. I'm sorry.

    Now it is uploaded. Please try to give me a solution.

    Pl.

    acsishere.
    =SUMPRODUCT((SUB2=N2)*(SUB3=Q2)*(SUB4=T2)*(SUB5=W2)*GROUP_CODE)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 04-28-2008, 01:26 PM #11
    acsishere is offline
    Registered User
    Join Date
    04-15-2008
    Location
    Tamil Nadu, India.
    MS-Off Ver
    Microsoft Office 2016
    Posts
    584

    Exclamation Dear Experts,

    Yes! both are working very nicely.

    Thanks a lot to both of you.

    Regards,

    acsishere.
    Reply With QuoteRegister To Reply
+ Reply to 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