8 Replies Latest reply on Aug 14, 2019 9:44 AM by Deepak Rai

# Combine two rows into one and sum their values

I have a huge file with many names and addresses. Some of them are written slightly differently, so they show up as separate entries, but they're actually the same place and should have only one entry. I need to combine the two slightly different entries into a single row, listing just the address or name that is the "correct" one and adding their values together.

So I would want to take something like this (very simplified):

And get something like this instead:

I can't seem to build a calculated field correctly for this. Many thanks for any insights!

• ###### 1. Re: Combine two rows into one and sum their values

Hi Cassandra,

What you are looking for is a fuzzy matching function.  Tableau Desktop doesn't have one, but there have been many different attempts at working around the problem.

Here's a post from the forum where a few suggestions are made:  Fuzzy Duplicates in data (Vendor Names)

There's also a post here about an approach someone took with Tableau Prep:  Can Tableau Prep do Fuzzy Matching? — OneNumber

Hope that helps!

-John

1 of 1 people found this helpful
• ###### 2. Re: Combine two rows into one and sum their values

Thank you, John! I guess I should feel better that there's a reason why I couldn't figure out how to make it work, but it's always frustrating to smack up against a program limitation. I'll see if those links can help me. (Unfortunately, I don't have Tableau Prep.)

• ###### 3. Re: Combine two rows into one and sum their values

Hi Cassandra, I realise this does not work well at scale, but are you familiar with the group members function ? Combined with the Alias feature this is an efficient method of matching data quickly.

HTH

Peter

1 of 1 people found this helpful
• ###### 4. Re: Combine two rows into one and sum their values

You're welcome and good luck!

• ###### 5. Re: Combine two rows into one and sum their values

Hi Cassandra,

Since you say you don't have Prep, so let's do it in Tableau;

Here it is:

Use These Calculations:

New Name:

{FIXED REPLACE(IF LEN(Name)-Len(REPLACE([Name]," ",""))>1

THEN SPLIT(Name," ",2) Else SPLIT(Name," ",1)END,"'",""):MAX(IF {FIXED REPLACE(IF LEN(Name)-Len(REPLACE([Name]," ",""))>1

THEN SPLIT(Name," ",2) Else SPLIT(Name," ",1)END,"'",""):MAX(LEN(Name))}=LEN(Name)

Then Name End)}

Using These you should get about 99-100% of your problem solved, so if you see some Comma or something else left in New Name, just tune my calculation accordingly, but it will work.

Thanks

Deepak

If it Helps, Pl Mark it Helpful and CORRECT to Close Thread

1 of 1 people found this helpful
• ###### 6. Re: Combine two rows into one and sum their values

Thank you for reminding me about that! That's a great solution for smaller files for sure.

• ###### 7. Re: Combine two rows into one and sum their values

Thank you, Deepak! Those are some new functions for me, so I'm going to have to go in and examine them for sure.

ETA: Wow, that's a lot of calculations to get there! I'm pretty sure I would NEVER have been able to think my way through that whole progression. I'm impressed.

• ###### 8. Re: Combine two rows into one and sum their values

Yes, lot of calculations and once I thought to give up as my initial thinking was not in right direction, but then light bulb glow up and here is the result.

Thanks

Deepak