Cell range with dynamic rows

[vanilz]

Hi Excel expert,

I have a table that row can be added depend on the needs. I would like to combine the rows into 1 cell.
A1 KY012345
A2 KY023456
A3 KY034567
A4 …
A5 …

I am using =CONCATENATE(A1:A3)
The result should be KY012345, KY023456, KY034567, …, … depends on the rows.
In this case, the number of rows cannot be determined. Hence, cannot use fix cell range.

Would you able to help with the formula to get A3 change according to the row added?

Thank you in advance.

 

[jasonb75]

If you format as a proper table then the range will be dynamic.

Book1
	A	B	C
1	List		A1 KY012345A2 KY023456A3 KY034567
2	A1 KY012345
3	A2 KY023456
4	A3 KY034567
Sheet5

Cell Formulas
Range	Formula
C1	C1	=CONCAT(Table1[List])

 

[vanilz]

Sorry, I'm quite new with excel formula. Hope to get the correct formula for below.
How to define it as table as mentioned above?

For this example, if user add row 7 with KY0999832, the result would not have the 4th account no.

 

[CA_Punit]

just press Ctrl+T and press ok
The ranges will ber converted into table and when you enter a new data in A7 will be be automatically incorporated in the result
and please dont select the cell indiviually in the concat function use like stated below

Book1
	B	C	D	E	F	G	H
2		List
3		A1 KY012345			A1 KY012345A2 KY023456A3 KY034567punit
4		A2 KY023456
5		A3 KY034567
6		punit
7
8
9
Sheet1

Cell Formulas
Range	Formula
F3	F3	=CONCAT(Table1 )

 

[vanilz]

Thank you.

When I tried to converted to table, the merged column split into 2 columns automatically. How to merge it?

I have macro to add row, however, when add new row, it is not incorporated in the result.

 

[CA_Punit]

Is your data merged???

Even if it is merged all the data will be transferred to the first column and then you can concat it. Leave the extra column generated

 

[jasonb75]

the merged column split into 2 columns automatically.

It wasn't merged in your screen capture.

Regardless of that, don't merge cells. When it comes to formulas merged cells are the root of all things evil.

For the formula, use =TEXTJOIN(", ",1,Table1

• ) instead, then you don't have to keep adding the commas.

 

[vanilz]

Thank you. I understood on above.

I adding rows with macro, but it did not incorporate with the table. How to set the new rows incorporate with the table?

 

[jasonb75]

I adding rows with macro

First merged cells, now macro. Is there any other missing information that you are likely to add later?