View original (French)

Transform an Excel table into a column

Solved
John -  
 John -
Hello everyone,

For my work, I'm looking to transform an Excel table into a column.

Example:

My table is:
A1 = 1 B1 = 2 C1 = 3
A2 = 4 B2 = 5 C2 = 6

I would like to have a column that fills up as follows:
A1 = 1
A2 = 2
A3 = 3
A4 = 4
A5 = 5
A6 = 6

I'm therefore looking for a function that could do this automatically since my table of values has 24 columns and 365 rows.

The best would be an Excel formula as I'm not familiar with VBA

Thank you very much for your help in advance :)

Configuration: Windows / Chrome 56.0.2924.87

4 answers

Answer 1 / 4
JvDo Posted messages 1924 Registration date   Status Member Last intervention   859
 
Hello everyone,

if your data is, as Philou10120 suggests, in A1:X365 and the result in AA1:AA8760, you select AA1:AA8760 and enter this array formula: 
=INDEX($A$1:$X$365;1+INT((ROW($1:$8760)-1)/24);1+MOD(ROW($1:$8760)-1;24))


best regards
3
Answer 2 / 4
cs_Le Pivert Posted messages 8437 Status Contributor 730
 
Hello,

Here is an example in VBA if you can't find a formula: 

Sub For_Each_Next_Range() Dim FL1 As Worksheet, Cell As Range, Range As Range Dim i As Integer i = 1 Set FL1 = Worksheets("Sheet1") 'adapt sheet With FL1 'Determination of the range of cells to read 'Can be written using the range object of the range 'For Each Cell In .Range("B3:E15") 'or using the Range object of the range Set Range = .Range("A1:E15") 'adapt range For Each Cell In Range 'Value of the read cell Range("F" & i) = Cell.Value 'adapt reception column i = i + 1 Next End With Set FL1 = Nothing Set Range = Nothing End Sub

@+ The Woodpecker
0
cs_Le Pivert Posted messages 8437 Status Contributor 730
 


Open the "Boucle" spreadsheet and click on GO, column A of the "test" spreadsheet will fill up.
To be adapted of course!

http://www.cjoint.com/c/GCvp5HXkGSQ

@+ The Woodpecker
0
Answer 3 / 4
PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   834
 
Hello John

Without a macro, if your data is in column A1:X365, place this formula in AA1

=INDIRECT(ADDRESS(COLUMN()-26,ROW(),1,1,"Sheet1"),1)

then sort this up to column OA and drag the row AA1:OA1 down to row 24

--
It is by forging that one becomes a blacksmith. - It is at the foot of the wall that one sees the bricklayer - one always learns from their mistakes.
0
John
 
Thank you cs_Le Pivert! I will try this solution if the Excel solution is too complicated...

Thanks also to PHILOU10120. However, I forgot to specify that the column I want to fill is located in another Excel file... Could we adapt your formula?

John
0
PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   834 > John
 
Replace Feuil1 with the file path
Open both workbooks and go to the formula above, select Feuil1, then go to the other workbook and click on the sheet where you want to retrieve the information, then enter
Feuil1 is replaced by the new sheet from the other workbook
0
John
 
I feel like it's not working :( In fact, I would like to create a single column that fills up by reading the table: first the values from the first row, then the second row in sequence, third row...

Is it clearer now?
0
Answer 4 / 4
Raymond PENTIER Posted messages 58207 Registration date   Status Contributor Last intervention   17 476
 
Hello John (and others).

Non-array formula, hence more complicated, allowing to display in a file a column of 8760 cells, based on a table of 24 x 365 cells located in Sheet1 of another file called Workbook5
=INDEX([Workbook5]Sheet1!$A$1:$X$365;CEILING(ROW() /24;0);IF(MOD(ROW();24)=0;24;MOD(ROW();24)))

--
Retirement is great! Especially in the Caribbean... :-)
Raymond (INSA, AFPA, CF/R)
0
John
 
Perfect!!

Thank you very much ;)
0