Formula to paste data in subsequent cells within the same column

[Tim_Excel_]

Hi forum!

I have been wrecking my brain over this, I hope you can help me out.

I have two sheets. In sheet 1, the user can mark at which interval a repair needs to be for certain machine codes. In sheet 2, 1 column shows the machine codes together with the interval codes (see example pictures). This column in the second sheet needs to be automised, in that a formula should automatically paste the codes+intervals when a user marks the intervals in sheet 1. However, were I to write something along the lines of

(IF(sheet1!$A2="1",CONCATENATE(sheet1!$D2,"_","03M",IF(sheet1!$B2="1",CONCATENATE(sheet1!$D2,"_","06M",""))

then only one of the two monthly repairs would appear in sheet 2, even if two are marked.
I'd like both to appear in the same column, maybe you know how to? I am stuck.

Tim

 

[Eric W]

It's not surprising you're having a tough time - these are tricky formulas. Given your sheet1, try this on sheet2:

	A	B	C
Code	Interval	Helper
03M
12M
06M
03M
06M

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]5000.01[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]5000.01[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5000.02[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5000.03[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5000.03[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]5[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=INDEX(Sheet1!$D$2:$D$100,MATCH(ROWS($A$2:$A2)-1,$C$2:$C$100))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=COUNTIF(Sheet1!$A$1:$C1,">0")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$A$1:$C$1,SMALL(IF(INDEX(Sheet1!$A$2:$C$100,MATCH(A2,Sheet1!$D$2:$D$100,0),0)<>"",COLUMN(Sheet1!$A$1:$C$1)),COUNTIF($A$2:$A2,A2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Adjust the ranges in the formulas as needed, and remember the formula in B2 is an array formula, requiring Control+Shift+Enter.

 

[Tim_Excel_]

Thank you Eric, this seems to do roughly what I want!

I'm still running into a few issues, however. For one, the formula has to be limited to a single column. This is because Sheet 2 will be parsed into a program that heavily relies on a specific set of columns and headers, which thus should not be altered.
I have tried to conjoin two seperate formulas using CONCATENATE, but this gives a remarkable result

The formula window makes it appear the code works, given the correct output. However, when I press "OK":

Perhaps you could also give me a brief explanation of the formulas? I'm far more familiar with VBA than I am with formulas. I'm at a loss what the helper column is for, for example.

 

[Peter_SSs]

I'm far more familiar with VBA than I am with formulas.

In that case, any reason not to do it by vba?


If you do want a formula solution, and you are on Office 365, then try this.
Sheet 1 exactly as you have shown.
Sheet2, formula below in A2 is copied down as far as you might need (could need more work if your data is very large. How big is it?)
Again, this is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A
1	CODE
2	5000.01_03M
3	5000.01_12M
4	5000.02_06M
5	5000.03_03M
6	5000.03_06M
7
8
Sheet2

 

[Tim_Excel_]

Peter, thank you. I could do it with VBA, it would be a lot easier in fact. However, I need a formula because it has to be updated every time the user changes a specific range of cells in sheet 1, and I cannot add code to the workbook (I could, but I'd have to use VBA from one workbook to add new macros to this workbook which is far from elegant).

I have anywhere from 1000 to a couple of thousand rows.

The code seems to do exactly what I want. I'm not sure how you do it, but you seem to make the impossible possible. Thank you a lot! I'll try to adjust it to my needs and report back if I find any new issues.

 

[Peter_SSs]

OK, see how it goes. It seems you have TEXTJOIN, but as an alternative here is another formula without helper column that only requires Excel 2010 or later and does not require the array formula entry.
Again, I'm not sure how it would go with a couple of thousand rows.

Excel Workbook
	A
1	CODE
2	5000.01_03M
3	5000.01_12M
4	5000.02_06M
5	5000.03_03M
6	5000.03_06M
7
Sheet2

I didn't really understand the issue with doing this by vba.
To do it with formulas, the formulas have to be pre-populated into the workbook whilst addressing the correct range(s).
What is the difference with pre-populating the workbook with the vba that addresses the correct range(s)?

 

[Tim_Excel_]

The code works, but I cannot get it to work once I use it in my real workbook, which has different columns. Perhaps, if possible, the formula could refrain from using the hearders to paste "03M, 06M" etc.? Those headers are absent in my workbook and were used as an example for this forum.

I'd appreciate it if you could provide me with a step by step explanation of the formula, so I fully understand it's workings- helps me in editing it to my needs!



The reason I can't use VBA:
Sheet 2 needs to be updated each time the user edits a range of cells in sheet 1. This would be possible with a Worksheet_Change event, but I am using another workbook to edit several other temporary workbooks, thus it would be illogical to have to put a piece of code in each of all those temporary workbooks

 

[Peter_SSs]

... I cannot get it to work once I use it in my real workbook, which has different columns. Perhaps, if possible, the formula could refrain from using the hearders to paste "03M, 06M" etc.? Those headers are absent in my workbook and were used as an example for this forum.

Perhaps then you could show us small sample of what your real data is like (& where it really is) together with the expected results?

 

[Tim_Excel_]

I would share a file with you were it not that my company has disabled their employees from uploading documents.. Therefore, here are some screengrabs again. I've left out a large portion of the data but what's important remains.

 

[Peter_SSs]

Assuming those two "1"s on row 5 of the first image were meant to be on row 4, try this

Excel Workbook
	D	U	AA	AD	AG
1	Code	03M Service	06M Service	09M Service	12M Service
2	700.000A	1			1
3	700.000B	1	1	1
4	0700.00G		1		1
Sheet1

Excel Workbook
	B
1	CODE
2	700.000A_03M
3	700.000A_12M
4	700.000B_03M
5	700.000B_06M
6	700.000B_09M
7	0700.00G_06M
8	0700.00G_12M
9
Sheet2