You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I have an excel file which has a code, These codes need suffixing with a length which is held in the next columns (up to 15). I need a way to get these lengths onto the end of the code. I can do it once for each column but I have a lot of different lengths and just to make it interesting there's a few zeroes thrown in which I don't want to include.
Simple example here with end result I want to achieve in column F
[img] 
[/img]
So it there a simple way to do this or am I condemned to a good few hours arsing around with filters and formulae/formulas.
Eh. Have you misaligned your last column?
And put too many rows in...
Nah I just typed them in manually as an example. In theory though from that little table I should get 9 individual codes.
No, I see what he means. The logic is "for each length on a row, if length is non-zero then concat code plus length into new field. Repeat for each row." What that is in Excelese I do not know.
Have three columns for the code results rather than one? Length1-code, Length2-code, Length3-code. That might it easier to then strip blanks and to manipulate into a single column as a next step. Once you've got that working, worry about optimising.
's what I'd try, I'm no Excel expert. There's probably a far more elegant way.
Sounds like concatinate is what you need, but you may need to format your zeros so they don't show, I'd probably have an additional column for the lengths where a zero would just give a blank
=CONCATENATE(A1,B1,C1,D1)
Something like this I would guess.
=IF( number<>0, prefix & number, "")
You'll end up with combined codes all over the place though, rather than in a single column.
NB "&" is a shortcut for concatenate
i would just do 'IF > 0' and then a concatenate to add them together. Also use 'TRIM' on the text part (abc etc) to get rid of any extra spaces. You are going to end up with 15 columns of data with blanks where there are zeros for the length
Is that what you were looking for?
=IFERROR(CONCATENATE(A1,B1,C1,D1),0)
if you want 0's instead of NA.
I see what you are doing. It seems like madness. If you have up to 15 codes per line item, you will have N*14 extra rows in your spreadsheet after you have run it, with only the end column populated. Are you really sure this is what you want ?
I can't for life of me think why you would want to do that, but I would do it with Python if there was a gun to my head, and I would wear gloves.
5 mins in VBA to knock up a script to generate a single list parsing a worksheet of input data....
Load the data to PowerQuery.
Select all the length columns, then use Transform->Unpivot Columns.
Remove the 0s.
Merge Columns Code & Value.
Load to new table.
RM.
5 mins in VBA to knock up a script to generate a single list parsing a worksheet of input data….
That, just the sort of thing VBA is good for
It seems like madness
It probably is but I'm just doing as requested. Stock holding moving from a parent code to individual codes for each length.
VBA and PowerQuery are a bit beyond me and I suspect it would take me as long to figure that out as it would to do it the long way round.
No need for power query etc.. just a bit of offset(...) and some indexes.
I'll send you this sheet as a sample if you want..
Edit - and if you want rid of the spaces - can just use index and small() to get a contiguous list.
On each row he wants a separate code generated for each non-zero length, not just a single code of them all concatted together.
But, but.. ermm
How will you use the result ? In your table the codes for A:5 DEF would be at F:8,F9,F:10 and there would be no way to relate them back to DEF at A:5 ?
I single comma or space separated value in the F column would make some sense, but I can't see the problem as you have described it being of any use to anyone.
eg A1: ABC -> F1: ABC1200, ABC2400, ABC3600
RM has it - a quick bit of power query and you'll have done something today to make you feel proud
That's what SAS and R are for 😉
I'd concatenate wide into new columns, with blank if a zero, then transpose the columns you generate. But I wouldn't start with XL really. Finally I'd sort them alphabetically in a new sheet.
On each row he wants a separate code generated for each non-zero length, not just a single code of them all concatted together.
My formulas do that..
How will you use the result ? In your table the codes for A:5 DEF would be at F:8,F9,F:10 and there would be no way to relate them back to DEF at A:5 ?
Relating them back isn't massively important.
I think @euain has it and it seems to be in the bounds of my ability. Can you email that example to matt dot lindley2 at gmail dot com ? Ta
On its way
I'd like to see that as well actually.
Would you care to email it me as well please, or stick a link here to a cloud save?
I use Excel so rarely. I'm better at VBA than Excel formulae.
I'd be using VBA as well here as it does feel like the formula is a bit of a cludge