Hi all, I'm hoping someone can help with my problem.
I need a formula which can concatenate the values in a row along with its corresponding column header and sub header descriptions, I've been trying to work with Offset, Lookup and Concatenate but am struggling to get a solution.
Formula_example.PNG
Many thanks for your help in advance!
If you have Excel 2016 you can do this with TEXTJOIN. Note this is an array formula entered with Ctrl+Shift+Enter:
wbd2018092701.PNG
WBD
Office 365 on Windows 11, looking for ✶ rep!
Hi, although a few users might have Excel 2016 the majority will still be using 2007 and this is a template that is sent in by many different teams, is there an alternative to this in 2007 (I assume using Concat or similar)?
Just to mention I need to import this spreadsheet with SSIS without doing any pre-import steps like running any vba etc...
Many thanks again!
Does UDF will do.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Limited to 2007 and no VBA all I have is brute force.
Formula:
Please Login or Register to view this content.
Dave
Hi, thanks for the effort!
I was thinking similar but was hoping there might be a way of using dynamic range with offset or something, but the problem is the columns can go over 100 so think the formula string would go over its limit, also the columns will be increasing once in a while (although that’s not the biggest issue).
Hi, thanks for the suggestion, however this is an option I was trying to avoid as it relies on users always using the explicit template file where as I’m sure there will be instances they copy the data into another file and re-save hence the UDF won’t be copied over, also the variance in file extension makes my SSIS import it’s a little more complex.Originally Posted by kvsrinivasamurthy
Although a UDF may have to be a very last resort 😖
Try this:
A B C D E F G 1 Sale Sale Return Sale Return 2 Market Formula TV Bike PC Fridge Cooker 3 Market A Sale-TV(2), Return-PC(5), Sale-Fridge(3) 2 5 3 4 Market B Sale-Bike(1), Sale-Fridge(2) 1 2 5 Market C Sale-TV(2), Sale-Bike(4) 2 4
Array Formulas Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Cell Formula B3 {=MID(CONCAT(IF($C3:$G3>0,", "&$C$1:$G$1&"-"&$C$2:$G$2&"("&$C3:$G3&")","")),3,1024)}
Note: Do not try and enter the {} manually yourself
WBD
@ rockyboy
Concatenating an array with formulas only (non 365 (2016)) remains a mystery. Most of us are hesitant to say it can't be done without VBA, but we suspect it is.
For now with the limitations of 2007 is appears VBA / UDF is your only option, and you should be able to make an *.xlmt macro enabled template.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks