Column to columns

[Jaymond Flurrie]

Does someone here have a ready code (formula or VBA) that would share data from a column to several columns.

So what I'm _not_ looking for is a "Text to columns" functionality, I know how to use that.

What I'm looking for is that when I import data from a website (like bank statement), it shows up like this:

5.10.2021
Food from store
-23,78
5.10.2021
Electricity bill
-15,15
3.10.2021
Food from store
-16

and that's in exactly one column. I want to split that to three columns, so that it turns out like this:
5.10.2021 Food from store -23,78
5.10.2021 Electricity bill -15,15
3.10.2021 Food from store -16

So every third row is a date, every third row is a description and every third row is an amount.

So a more generic definition: Split data in a column into X columns

I'm sure there's ready made code for this, I just don't know what to search with in order to avoid the "use text to columns functionality"-answers.

Here's a start for it:

Assuming the data is in Column A, starting from row 1, to Column B formula to get the first column values:
=IF(MOD(ROW(A1)+2;3)=0;A1;"")
to Column C to get the second column values:
=IF(MOD(ROW(A1)+1;3)=0;A1;"")
and to Column D to get the third column values:
=IF(MOD(ROW(A1);3)=0;A1;"")

but this returns values to every row, with two of the columns being empty. So this is not ready, although it is a start.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Jaymond Flurrie]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

16.0.14430.20154 32-bit

 

[Fluff]

That is the build not the actual version which will be either 2016, 2019 or 365. I suspect it's 365. Can you please confirm which?

 

[Jaymond Flurrie]

That is the build not the actual version which will be either 2016, 2019 or 365. I suspect it's 365. Can you please confirm which?

2016.

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D
1	abc	abc	def	xyz
2	def	abc1	ddd1	fff1
3	xyz
4	abc1
5	ddd1
6	fff1
7
Lists

Cell Formulas
Range	Formula
B1:D3	B1	=IFERROR(INDEX($A$1:$A$6,(ROWS(B$1:B1)-1)*3+COLUMNS($B1:B1)),"")

 

[Jaymond Flurrie]

Works great and seems easy enough to expand to either more rows or even to more columns!

 

[Fluff]

You're welcome & thanks for the feedback.