Hi All.
This is in reference to https://www.excelforum.com/excel-gen...f-payment.html
posted by Shan54321, where solution given is for the newer excel versions.
For this formula,
=IF(G7="","",TEXTJOIN("/",TRUE,IF(FREQUENCY(IF(G7=$B$7:$B$11,MATCH($C$7:$C$11,$C$7:$C$11,0)),ROW($B$7:$B$11)-MIN(ROW($B$7:$B$11))+1),$C$7:$C$11,"")))
What will be the 2007 equivalent?
Thanks.
could you show example of your file. Without it the alternatives are: additional helper column, VBA , PowerQuery
also the trick can be used
Please Login or Register to view this content.
Last edited by AliGW; 04-05-2020 at 02:20 AM. Reason: Typo corrected.
Please try I7
=MID(IF(COUNTIFS($B7:$B11,G7,C$7:C$11,"Cash"),"/Cash","")&IF(COUNTIFS($B$7:$B$11,G7,$C$7:$C$11,"Credit Card"),"/Credit_Card","")&IF(COUNTIFS(B$7:B$11,G7,C$7:C$11,"Cheque"),"/Cheque",""),2,30)
Hi.
BMV, thanks for the help but excel indicate error in the formula.
Bo_Ry thanks also for the help. your formula works. I think this part was overlooked =MID(IF(COUNTIFS($B7:$B11, I've changed it to $B$7:$B$11.
Quick question, should I change the "mode" part into another data input, and its more than 3 (cash/cheque/cd), say 6, i have to copy the countif 6 times and so what will be the end of the formula ,(for the 2,30)?
Thanks
Change 30 to 300 or a maximum length of all 6 modes.
But if you have more like 20 modes, copy 20 countif is not fun
Try with helper column
E7
=SUBSTITUTE(IFERROR(C7&"/"&VLOOKUP(B7,B8:E11,4,),C7),"/"&C7,)
J7
=VLOOKUP(G7,$B$7:$E$12,4,0)
Separator ; should be changed to ,excel indicate error in the formula
However once again. Your file is your file and there could be options.
BMV
I'm open to other solution. Is this what you meant?
=REPLACE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("/Cash/Credit Card/Cheque","/Cash","",1+COUNTIFS($B$7:$B$11,G7,$C$7:$C$11,"Cash")),"/Credit Card","",1+COUNTIFS($B$7:$B$11,G7,$C$7:$C$11,"Credit Card")),"/Cheque","",1+COUNTIFS($B$7:$B$11,G7,$C$7:$C$11,"Cheque")),1,1)?
"You've entered too few arguments for this function" error
Bo_Ry
Thanks for the other option.
Stay Safe.
See attach. But the method is approximately the same as consolidate and maybe worse.I'm open to other solution. Is this what you meant?
Okay BMV, Thanks. Take Care.
If you still want help with this, please upload a sample workbook. Instructions at the top of the page.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks