+ Reply to Thread
Results 1 to 7 of 7

Need formula to extract account ID, per unique sub-account

  1. 03-21-2019, 08:23 AM #1
    ierosadopr
    ierosadopr is offline
    Registered User
    Join Date
    03-01-2019
    Location
    Europe
    MS-Off Ver
    2016
    Posts
    16

    Need formula to extract account ID, per unique sub-account

    Hello everyone,

    I would like help in creating a formula that does the following:

    a. extract IDs from a table, with all their all respective unique sub accounts coming from that same table, while ignoring all "AA" sub accounts.
    2. ignore blanks within the table that contains the data.
    3. do not show spaces between results.

    I have uploaded a file illustrating the expected result, attached to this message.

    cheers!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-21-2019, 08:45 AM #2
    XLent
    XLent is offline
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Need formula to extract account ID, per unique sub-account

    Given XL2016 you could try:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    note: I've assumed ; delimiter so, if necessary, swap for appropriate delimiter as your locale requires.

    if you're using O365 rather than XL2016 you could also use TEXTJOIN

    EDIT:
    apologies, I believe I have misunderstood your requirement which is to list the unique ID & sub-account combinations per row rather than, the above, concatenating the unique sub-accounts for a given ID into a single row (e.g. TUR5J8 for Nichole)
    Last edited by XLent; 03-21-2019 at 08:54 AM.
    Reply With QuoteRegister To Reply
  3. 03-21-2019, 08:54 AM #3
    ierosadopr
    ierosadopr is offline
    Registered User
    Join Date
    03-01-2019
    Location
    Europe
    MS-Off Ver
    2016
    Posts
    16

    Re: Need formula to extract account ID, per unique sub-account

    Pasted on E2 as instructed, but gives a name error.

    Please allow me to clarify that neither results from D column, nor E column, are available initially. I need for both to be calculated automatically.
    Reply With QuoteRegister To Reply
  4. 03-21-2019, 09:29 AM #4
    XLent
    XLent is offline
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Need formula to extract account ID, per unique sub-account

    sorry, I was away from my machine...

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    I've attached a working example such that you can see per your local settings.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 03-21-2019, 09:35 AM #5
    ierosadopr
    ierosadopr is offline
    Registered User
    Join Date
    03-01-2019
    Location
    Europe
    MS-Off Ver
    2016
    Posts
    16

    Re: Need formula to extract account ID, per unique sub-account

    I have no words to describe how amazed I am by your skills. Thank you so very much for your assistance.
    Reply With QuoteRegister To Reply
  6. 03-21-2019, 10:33 AM #6
    Olly
    Olly is offline
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Need formula to extract account ID, per unique sub-account

    This is really simple with Power Query (Get & Transform Data):

    Please Login or Register  to view this content.
    Worked example: https://excel.solutions/xlf_1269310/
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...
    Reply With QuoteRegister To Reply
  7. 03-21-2019, 10:38 AM #7
    ierosadopr
    ierosadopr is offline
    Registered User
    Join Date
    03-01-2019
    Location
    Europe
    MS-Off Ver
    2016
    Posts
    16

    Re: Need formula to extract account ID, per unique sub-account

    Thank you very much, I will look into it asap.
    Cheers!
    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)

Similar Threads

  1. Suming the total for an account from 3 worksheets after looking up the account number
    By MelPB in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-24-2018, 10:21 AM
  2. Need a COUNTIF formula that takes unique values into account
    By Meggo12 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-05-2016, 04:17 PM
  3. Change the picture in the linked social media account manage page on excel (user account)
    By BOJMARLOS in forum Excel General
    Replies: 0
    Last Post: 03-14-2015, 04:44 PM
  4. [SOLVED] Remove an account range form a column with account numbers.
    By kokapelly in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 09-09-2013, 11:34 AM
  5. [SOLVED] How to censor account numbers based on how many characters are in the account number
    By Mcorydon in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-25-2013, 01:06 PM
  6. Complex If Statement needed to Calculate Date Range from Create Account to Stop Account
    By trankim in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-07-2012, 12:00 PM
  7. How to sum charges to each account made thru year on a monthly basis by account-Excel 2010
    By TomMan in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-12-2012, 05:30 AM

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