Cell range with dynamic rows

vanilz

New Member
Joined
Sep 19, 2019
Messages
9
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,599
Office Version
  1. 365
Platform
  1. Windows
If you format as a proper table then the range will be dynamic.
Book1
ABC
1ListA1 KY012345A2 KY023456A3 KY034567
2A1 KY012345
3A2 KY023456
4A3 KY034567
Sheet5
Cell Formulas
RangeFormula
C1C1=CONCAT(Table1[List])
 

vanilz

New Member
Joined
Sep 19, 2019
Messages
9
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.

1594553888835.png
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
899
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGH
2List
3A1 KY012345A1 KY012345A2 KY023456A3 KY034567punit
4A2 KY023456
5A3 KY034567
6punit
7
8
9
Sheet1
Cell Formulas
RangeFormula
F3F3=CONCAT(Table1
  • )
 

vanilz

New Member
Joined
Sep 19, 2019
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Nov 18, 2019
Messages
899
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,599
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Sep 19, 2019
Messages
9
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,352
Messages
5,635,785
Members
416,882
Latest member
ericvrealty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top