Excel formula to covert groups of colums to a single row

[Dazza01]

I am struggling with a formaula, i have tried a number of different calculations, but cannot seem to figure it out, below is the data table, i would prefer not to use a VBA as i have limited experience.

What i am trying to achieve is below

 

[hiker95]

Dazza01,

Welcome to the MrExcel forum.

You are posting two pictures/images.

This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually.


I would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com


If you are not able to provide the above, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this is any use. Formula in A7 is copied across and down. You may then need to reformat the first column as date and/or the 4th column as number.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	1/01/2016	A	AA	1000	2/01/2016	B	BB	1100	3/01/2016	C	CC	1200
2	4/01/2016	D	DD	1300	5/01/2016	E	EE	1400	6/01/2016	F	FF	1500
3	7/01/2016	G	GG	1600	8/01/2016	H	HH	1700	9/01/2016	I	II	1800
4	10/01/2016	J	JJ	1900	11/01/2016	K	KK	2000	12/01/2016	L	LL	2010
5
6
7	1/01/2016	A	AA	1000
8	2/01/2016	B	BB	1100
9	3/01/2016	C	CC	1200
10	4/01/2016	D	DD	1300
11	5/01/2016	E	EE	1400
12	6/01/2016	F	FF	1500
13	7/01/2016	G	GG	1600
14	8/01/2016	H	HH	1700
15	9/01/2016	I	II	1800
16	10/01/2016	J	JJ	1900
17	11/01/2016	K	KK	2000
18	12/01/2016	L	LL	2010
19
Rearrange

 

[hiker95]

Dazza01,

Here is a macro solution for you to consider, based on your displayed flat text, that will adjust to the number of raw data rows, and, columns, and, that uses two arrays in memory.

Sample raw data in the active worksheet:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	01/01/2016	A	AA	1000	02/01/2016	B	BB	1100	03/01/2016	C	CC	1200
2	2	04/01/2016	D	DD	1300	05/01/2016	E	EE	1400	06/01/2016	F	FF	1500
3	3	07/06/2016	G	GG	1600	08/01/2016	H	HH	1700	09/01/2016	I	II	1800
4	4	10/01/2016	J	JJ	1900	11/01/2016	K	KK	2000	12/01/2016	L	LL	2010
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sheet1

And, after the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	01/01/2016	A	AA	1000	02/01/2016	B	BB	1100	03/01/2016	C	CC	1200
2	2	04/01/2016	D	DD	1300	05/01/2016	E	EE	1400	06/01/2016	F	FF	1500
3	3	07/06/2016	G	GG	1600	08/01/2016	H	HH	1700	09/01/2016	I	II	1800
4	4	10/01/2016	J	JJ	1900	11/01/2016	K	KK	2000	12/01/2016	L	LL	2010
5
6		DATE	F/N	L/N	VALUE
7	1	01/01/2016	A	AA	1000
8	1	02/01/2016	B	BB	1100
9	1	03/01/2016	C	CC	1200
10	2	04/01/2016	D	DD	1300
11	2	05/01/2016	E	EE	1400
12	2	06/01/2016	F	FF	1500
13	3	07/06/2016	G	GG	1600
14	3	08/01/2016	H	HH	1700
15	3	09/01/2016	I	II	1800
16	4	10/01/2016	J	JJ	1900
17	4	11/01/2016	K	KK	2000
18	4	12/01/2016	L	LL	2010
19
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ConvertGroups()
' hiker95, 11/06/2016, ME974416
Dim a As Variant, i As Long, c As Long, lr As Long, lur As Long, lc As Long, n As Long
Dim o As Variant, j As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(1, 1).End(xlDown).Row
  lur = .Cells(.Rows.Count, 1).End(xlUp).Row
  If lur > lr Then
    .Rows(lr + 2).Resize(lur).ClearContents
  End If
  lc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  n = (lr * (lc - 1) / 4) + 1
  ReDim o(1 To n, 1 To 5)
  j = j + 1
  o(j, 1) = "": o(j, 2) = "DATE": o(j, 3) = "F/N"
  o(j, 4) = "L/N": o(j, 5) = "VALUE"
  For i = LBound(a, 1) To UBound(a, 1) Step 1
    For c = 2 To UBound(a, 2) Step 4
      j = j + 1
      o(j, 1) = a(i, 1)
      o(j, 2) = a(i, c)
      o(j, 3) = a(i, c + 1)
      o(j, 4) = a(i, c + 2)
      o(j, 5) = a(i, c + 3)
    Next c
  Next i
  .Cells(lr + 2, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Range("B" & lr + 3 & ":B" & UBound(o, 1) + lr + 3).NumberFormat = "mm/dd/yyyy"
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ConvertGroups macro.