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 ColumnAccountClient NameSubAccountParent Name111000ABC Inc111000ABC Inc111000BDC Inc111001ABC Inc111000ABC LLC111002ABC Inc111000ABC Co111003ABC Inc222000Help Inc222000Help Inc222000Help LLC222001Help Inc222000Help Me222003Help Inc222000Help Me Please222002Help Inc333000Need Assistance333000Need Assistance444000S.O.S444000S.O.S

Adding column with existing data

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

Workflow

Datasets

Input

Help

Tableau

Preparation

Accepted answers

summarizer

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!!!

Answer 3.jpg

All comments

Greg_Murray

I think this is what you're looking for. It uses a filter tool to find accounts and sub accounts that are the same and then assigns that account name to all of the matching accounts in the join.

cheers,

Greg

assigning account name.yxmd

GarthMiles

would this be what you're looking for? (see attached)

Screen Shot 2018-07-27 at 10.26.40 AM.png

add-col-to-existing-data.yxmd

vishwa_0308

I would go with using mullti-row formula tool:

GarthMiles

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'.

Screen Shot 2018-07-28 at 1.10.38 PM.png

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)

summarizer

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!!!

Answer 3.jpg

bcastle88

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703