Hey Guys,
I'm having an issue trying to solve what would be a fairly easy fix in Excel. Our billing system assigns a single account number and name and then sub-accounts that can have distinct client names. I'm trying to create a 4th column where the account name is listed for all of the sub-accounts. Is there a tool or formula that could assist with this? Notice in the attached image each account number is also a Sub-Account
New Column | |||
Account | Client Name | SubAccount | Parent Name |
111000 | ABC Inc | 111000 | ABC Inc |
111000 | BDC Inc | 111001 | ABC Inc |
111000 | ABC LLC | 111002 | ABC Inc |
111000 | ABC Co | 111003 | ABC Inc |
222000 | Help Inc | 222000 | Help Inc |
222000 | Help LLC | 222001 | Help Inc |
222000 | Help Me | 222003 | Help Inc |
222000 | Help Me Please | 222002 | Help Inc |
333000 | Need Assistance | 333000 | Need Assistance |
444000 | S.O.S | 444000 | S.O.S |
Solved! Go to Solution.
I would go with using mullti-row formula tool:
I'm not sure that would work, @vishwa_0308. Based on the sample data provided, there's no guarantee that [SubAccount] will match [Account] with an exact match function. In fact, the outcome will more often be a 'no match'.
That being said, your IF-ELSE statement could be simplified by selecting [Account] as a 'Group By' option (i.e., no need for the 'elseif' clause)
This way works. I would sort your data by Account and Sub Account ascending - since it seems your main account number and the first sub account (ending in all zeroes) is the Parent name you are wanting in your new column.
Then Summarize it by grouping by the Account, and then choosing the first of the Account names (as you can see in the Summary configuration below).
Then join it up with your original data by the Account number. Best of luck!!!
Thanks so much for the help everyone. I always find that many of the wrong or half solutions always end up assisting me with other problems i'm trying to solve.
This is such a great community.
BC