From the Miami Power Excel seminar - when you download Quickbooks customer list to Excel, some rows have the customer name in the address field and others do not. How do you remove the duplicate name before joining text?
Transcript of the video:
The MrExcel podcast is sponsored by Easy Excel.
MrExcel podcast episode 1931, QuickBooks, Concatenate, but avoid name, name, address.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Last week, I did that a power Excel seminar down in Miami.
Lots of great questions come out of this seminar.
So all this week I'll be showing questions that came after that seminar.
And I instantly recognized this question when it came up, because it happens to me as well.
Someone was using QuickBooks for their accounting software.
And when you download the customer list from QuickBooks, we have two fields.
We have a name, field, and an address field. And for whatever reason over the years, um, many of the items in the address field have the name as the starting value.
Uh, but then others do not, right.
I don't know if QuickBooks changed how they handle this over the years, or I'm just inconsistent about the way that I enter data or it's whether it was important or not important, but, you know, I, I recognize this is the one we can catenate column a and column B over here.
Uh, sometimes we're getting the name twice, like Boeing, Boeing, uh, Ford, Ford, and so on.
And so how do you know whether or not to concatenate a and B or just use only B?
And so my solution here, uh, was to say equal if the left of column B and how many characters do I want to check?
I want to check the length of column.
So look at the left most characters of column B and see if that's equal to the name over in a two.
And if it is then I simply want column B.
Otherwise I want column a ampersand quote space, quote, ampersand column B.
close the if statement and, uh, with that simple little bit of logic there, copy of down at smart enough to pull over the customer name and then the address.
if the address doesn't start with the customer name, otherwise, uh, it pulls over just the address, which has the customer name.
Alright.
All right.
So, uh, two different functions there, uh, left and Len inside of an if function.
So I guess a total of three functions.
I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
MrExcel podcast episode 1931, QuickBooks, Concatenate, but avoid name, name, address.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Last week, I did that a power Excel seminar down in Miami.
Lots of great questions come out of this seminar.
So all this week I'll be showing questions that came after that seminar.
And I instantly recognized this question when it came up, because it happens to me as well.
Someone was using QuickBooks for their accounting software.
And when you download the customer list from QuickBooks, we have two fields.
We have a name, field, and an address field. And for whatever reason over the years, um, many of the items in the address field have the name as the starting value.
Uh, but then others do not, right.
I don't know if QuickBooks changed how they handle this over the years, or I'm just inconsistent about the way that I enter data or it's whether it was important or not important, but, you know, I, I recognize this is the one we can catenate column a and column B over here.
Uh, sometimes we're getting the name twice, like Boeing, Boeing, uh, Ford, Ford, and so on.
And so how do you know whether or not to concatenate a and B or just use only B?
And so my solution here, uh, was to say equal if the left of column B and how many characters do I want to check?
I want to check the length of column.
So look at the left most characters of column B and see if that's equal to the name over in a two.
And if it is then I simply want column B.
Otherwise I want column a ampersand quote space, quote, ampersand column B.
close the if statement and, uh, with that simple little bit of logic there, copy of down at smart enough to pull over the customer name and then the address.
if the address doesn't start with the customer name, otherwise, uh, it pulls over just the address, which has the customer name.
Alright.
All right.
So, uh, two different functions there, uh, left and Len inside of an if function.
So I guess a total of three functions.
I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
