Can I create a concatenate if fomula?

Hi,

I have a problem - I have a spreadsheet that shows a list of customers, together with their outstanding invoices. I have a formula to SUMIFS the balance of invoices based on the month they were due, but I therefore have ended up with multiple entries per customer if their debt is due from multiple months.

What I am looking to do is concatenate the month/balances together IF the customer name in column A is the same (ie multiple entries) - kind of like a sumif, but to join text not numbers.

My customer names are in column A,
My total outstanding debt is in column L (irrespective of month)
My month due & balance (text format) are in column N.

I want to end up with a report that display customer names only once, along with their total outstanding debt, and a display of months & balances due.

Please help!?!

Attach a sample workbook (desensitize it first) - the FAQ describes how to.

Pete

This is the sample sheet that I hope explains what I'm trying to do....

123.xlsx

I did not check your formulas because it looked like they were doing what you described. Please check the solution attached as I think it meets your needed criteria.

Mattbau43 - thanks for that - however, what I am hoping to end up with is more like the second tab of the attached file... that's why I am hoping to be able to CONCATENATE or something(!) the text month / balances together into 1 cell to the right of the total balance due...

Is this possible???

Thanks Leon, but I'm not sure I understand how this works. Can you explain further? If it helps, I've attached the sample worksheet below, which may explain more what I'm trying to do..

I think that you are wanting to have all months due and all balances due to view in one cell is this correct?

Hi Matt - effectively yes.. In Sheet26, (column N) I have created the text of the months & balances - now what i want to do is somehow string together these in one cell, so for example in my report Customer 2 would show in 1 row:

Customer 2 11248.58 Sep - £239.78 / Oct - £352.8 / Feb - £10656

The total balance being the SUMIF in column L in sheet26, and the split being a concatenation of the SUMIFS by month in column M, with Customer name (column A) being the criteria.

If this is the case then I am unaware of a solution. This would be a VBA question I believe. If I am incorrect and someone knows of a solution please post it so that I can learn.

Sorry I could not be of more help on this.

Thanks for looking Mattbau43. Really appreciate your time :-)

Can anybody help???! This is doing my head in!

Ultimately the report is run through VBA macros, so if this is the only way of doing it, I would be hugely grateful :-)

Put this formula in O2 of Sheet26:

=IF(B2="","-",MID(B2,FIND(" ",B2)+1,255)&"_"&COUNTIF(B$2:B2,B2))

then copy it down to beyond your data. This shows that there are no more than 4 records for each customer.

Then in Sheet1 C3 you can have this formula:

=SUBSTITUTE(SUBSTITUTE(IFERROR(INDEX(Sheet26!K:K,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_1",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_1",Sheet26!O:O,0))&" / ","")&IFERROR(INDEX(Sheet26!K:K,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_2",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_2",Sheet26!O:O,0))&" / ","")&IFERROR(INDEX(Sheet26!K:K,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_3",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_3",Sheet26!O:O,0))&" / ","")&IFERROR(INDEX(Sheet26!K:K,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_4",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_4",Sheet26!O:O,0))&" / ","")&"x","/ x",""),"x","")

which will accommodate up to 4 records per customer. Here's what it looks like with a few manual breaks in it:

=SUBSTITUTE(SUBSTITUTE(
IFERROR(INDEX(Sheet26!K:K,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_1",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_1",Sheet26!O:O,0))&" / ","")&
IFERROR(INDEX(Sheet26!K:K,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_2",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_2",Sheet26!O:O,0))&" / ","")&
IFERROR(INDEX(Sheet26!K:K,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_3",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_3",Sheet26!O:O,0))&" / ","")&
IFERROR(INDEX(Sheet26!K:K,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_4",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(MID(A2,FIND(" ",A2)+1,255)&"_4",Sheet26!O:O,0))&" / ","")&
"x","/ x",""),"x","")

so hopefully you can see the structure a bit more easily. Copy this down to the bottom of your list.

You have some months missing from column K of Sheet26, with obvious impacts on how this displays.

I've attached the file I worked on to show it working.

Hope this helps.

Pete

Wow. Nice work Pete. Way over my head. Thank you!

Nah! the first formula identifies each customer and tags each customers' record with a unique number. The other formula just picks up the month and amount for up to 4 records and concatenates them together, by matching on those unique numbers - look for the "_1", "_2" etc. in the middle (most of it was copy/paste, with just those changes to be made).

Thanks for the comment, though.

Pete

I have to tear this apart and learn it now. You may get some follow up questions Pete. Actually I see several follow up questions coming.....

Pete_UK this is fantastic! I was going to filter unique records based on the month/balance (column N), therefore unless my customers have more than 4 months due (unlikely), then this should work perfectly!

Just 1 question - given that my customers have real names (often more than 1 word), the 1st formula is giving me names like Packaging Limited_1 - this shouldn't make a difference to the end result should it?

Pete I can't wrap my head around this one.

If you get a free minute I would really appreciate a bit more info on how you did this. Maybe a few notes in the file or a video?

Anything that you are able to com up with is appreciated.

I have to go out shortly, so I'll give a fuller explanation later on.

@alejandro

Yes, I thought you might have actual names in your real file. In that case it might be better to use, say, the first 5 letters of the name (if these are unique) or the unique code that you have in column A of Sheet26 (using INDEX/MATCH) - can you also put that unique code in Sheet1, so we wouldn't have to match indirectly on it? Or even in a separate sheet which lists all codes with their customer names, as Sheet26 only includes a few customers' transactions.

I'll get back to you (both) later on.

Pete

To answer alejandro first, as it is his thread, I have made some changes to the workbook to try to simulate you using different customer names. First of all, I've added a new sheet, Ref_data, with a list of the customer names in column B that you had in Sheet1. I've made up some fictitious names for the first 12, and also put the Account number for those 12 in column A.

This then allowed me to introduce (and correct) some formulae in Sheet26 - I put this formula in B2:

=VLOOKUP(A2,Ref_data!A:B,2,0)

and copied it down to give me those made-up names instead of Customer 1, Customer 2 etc. I've also put this formula in K2 and copied it down:

=IF(J2="","",TEXT(J2,"mmm"))

so that you don't have to type in the month names. I've corrected the formula in L2 to this:

=SUMIF(B:B,B2,H:H)

and in M2 to this:

=SUMIFS(H:H,A:A,A2,K:K,K2)

and I have changed the formula in O2 to this:

=IF(A2="","-",A2&"_"&COUNTIF(A$2:A2,A2))

Obviously, these are copied down. The formula in O2 now uses the Account code to identify the customer, and that is followed by a sequential number for each record, thus giving a unique reference to each record.

In Sheet1 I have introduced a new column B and used it to return the customer's account number by means of this formula:

=INDEX(Ref_data!A:A,MATCH(A2,Ref_data!B:B,0))

I have only copied this down for the first 12 customers, as the account code doesn't exist in the Ref_data sheet for the other customers. Note that I have also sorted the customer names for the first 12, to show you that it doesn't matter what order they are in relative to Sheet26.

The formula in D2 has been changed to this:

=SUBSTITUTE(SUBSTITUTE(IFERROR(INDEX(Sheet26!K:K,MATCH(B2&"_1",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(B2&"_1",Sheet26!O:O,0))&" / ","")&IFERROR(INDEX(Sheet26!K:K,MATCH(B2&"_2",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(B2&"_2",Sheet26!O:O,0))&" / ","")&IFERROR(INDEX(Sheet26!K:K,MATCH(B2&"_3",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(B2&"_3",Sheet26!O:O,0))&" / ","")&IFERROR(INDEX(Sheet26!K:K,MATCH(B2&"_4",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(B2&"_4",Sheet26!O:O,0))&" / ","")&"x","/ x",""),"x","")

or, by introducing a few manual breaks as before, for easier understanding:

=SUBSTITUTE(SUBSTITUTE(
IFERROR(INDEX(Sheet26!K:K,MATCH(B2&"_1",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(B2&"_1",Sheet26!O:O,0))&" / ","")&
IFERROR(INDEX(Sheet26!K:K,MATCH(B2&"_2",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(B2&"_2",Sheet26!O:O,0))&" / ","")&
IFERROR(INDEX(Sheet26!K:K,MATCH(B2&"_3",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(B2&"_3",Sheet26!O:O,0))&" / ","")&
IFERROR(INDEX(Sheet26!K:K,MATCH(B2&"_4",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(B2&"_4",Sheet26!O:O,0))&" / ","")&
"x","/ x",""),"x","")

I'll explain this in a bit more detail in a separate post (after I've had a break) for the benefit of mattbau.


If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Now to explain the conditional concatenation following mattbau's request ...

If you wanted to join four cells together with a comma and space between them then you might initially use:

=A1&", "&B1&", "&C1&", "&D1

which is fine as long as all those cells contain values. But if one or more of them are empty and you want to avoid having multiple comma-spaces, then you could refine it to this:

=IF(A1="","",A1&", ")&IF(B1="","",B1&", ")&IF(C1="","",C1&", ")&IF(D1="","",D1&", ")

This doesn't quite work as you will always have a comma-space after the last value listed, so you can get rid of that by tagging on another character (e.g. x) and using SUBSTITUTE to remove the comma-space-x from the end:

=SUBSTITUTE(IF(A1="","",A1&", ")&IF(B1="","",B1&", ")&IF(C1="","",C1&", ")&IF(D1="","",D1&", ")&"x",", x","")

but again this doesn't work if all those cells are empty as you will still have the x on its own, so another SUBSTITUTE can get rid of that:

=SUBSTITUTE(SUBSTITUTE(IF(A1="","",A1&", ")&IF(B1="","",B1&", ")&IF(C1="","",C1&", ")&IF(D1="","",D1&", ")&"x",", x",""),"x","")

That is basically what I have above, although the cells A1, B1 etc. are actually more complex expressions - the IF term for A1 is equivalent to this:

IFERROR(INDEX(Sheet26!K:K,MATCH(B2&"_1",Sheet26!O:O,0))&" - £"&INDEX(Sheet26!H:H,MATCH(B2&"_1",Sheet26!O:O,0))&" / ","")

You can see that this is basically made up of two INDEX/MATCH functions, joined together with " - £" in the middle, and with " / " tagged on to the end instead of the comma-space. The whole thing is wrapped in IFERROR in case there is not an exact match, in which case "" is returned for that term. The first INDEX/MATCH is returning the month (as it is looking at column K in Sheet26) and the second one is returning the cost from column H. What we are trying to find in both cases is B2&"_1" in column O of Sheet26, where B2 is the account code - the other terms are looking for B2&"_2", and so on, i.e. the subsequent records for that account code.

The clever bit, really, is the formula in column O of Sheet26, i.e.:

=IF(A2="","-",A2&"_"&COUNTIF(A$2:A2,A2))

This adds on a sequential number to the account code (separated by an underscore for clarity), because as the formula is copied down the range in the COUNTIF function becomes COUNTIF(A$2:A3,A3), then COUNTIF(A$2:A4,A4), and so on, so this will give a unique number for whatever is in column A on a particular row.

Anyway, that's a long enough explanation - hope it makes a bit more sense to you now. You can always click on the star to show your appreciation, even though this is not your thread.

Hope this helps.

Pete

Great work. Thank you for taking the time to explain. I will take the next three hours to digest that

Pete_UK that is absolutely brilliant! I'm so impressed - think you've just taught me more about excel in that one reply than I ever knew before :-)

Thanks so much - I'd never have dreampt of doing it that way.