Series based on row and column combination

Question

I have an Excel with rows and columns as below:

I need to build a series like below in Excel with two columns as shown:

I have a huge data set where rows are dates and columns are data in half hours.

Which would be the best method?

Answer 1

I would suggest using the INDIRECT function thus:

In the column when you want to have the Row labels, put this formula:

=INDIRECT("R" & MOD(ROW()-1, COUNTA(A:A))+2 & "C1",FALSE)

Here A:A refers to the column where your row labels are stored and +2 is offset to the first row with a label.

In the column where you want the Column labels, put:

=INDIRECT("R1C" & ROUNDDOWN((ROW()-1)/COUNTA($B$1:$D$1),0)+2,FALSE)

Here $B$1:$D$1 refers to the range with your column labels, and +2 is again offset to the first column label.

Answer 2

Assuming your data is in the range C7:F12

We’ll need three fields to show the resulting series: Row, Col and Data

Row: in cell H7 enter this formula and copy till the last record:

=IF(EXACT(H6,H$6),1,
IF(EXACT($I7,CHAR(133)),"",
IF($I7=1,SUM(1,H6),H6)))

Col: in cell I7 enter this formula and copy till the last record:

=IF(EXACT(I6,I$6),1,
IF(EXACT(I6,CHAR(133)),CHAR(133),
IF(I6=COLUMNS($C$7:$F$12),
IF(H6=ROWS($C$7:$F$12),CHAR(133),1),
SUM(1,I6))))

Data: in cell J7 enter this formula and copy till the last record:

=IF(EXACT($I7,CHAR(133)),"",
INDEX($C$7:$F$12,$H7,$I7))

Answer 3

Posting a revised answer following sample data provided by user (left prior answer as it might be useful to other users)

Assuming your data is in the range C6:K11

We’ll need four fields to show the resulting series: Row, Col, 'DateandTime`

Row: in cell M7 enter this formula and copy till the last record:

=IF(EXACT(M6,M$6),1,
IF(EXACT($N7,CHAR(133)),"",
IF($N7=1,SUM(1,M6),M6)))

Col: in cell N7 enter this formula and copy till the last record:

=IF(EXACT(N6,N$6),1,
IF(EXACT(N6,CHAR(133)),CHAR(133),
IF(N6=COLUMNS($C$6:$K$6),
IF(M6=ROWS($B$7:$B$11),CHAR(133),1),
SUM(1,N6))))

Date: in cell O7 enter this formula and copy till the last record:

=IF(EXACT($N7,CHAR(133)),"",
INDEX($B$7:$B$11,$M7,0))

Time: in cell P7 enter this formula and copy till the last record:

=IF(EXACT($N7,CHAR(133)),"",
INDEX($C$6:$K$6,0,$N7))

Fields Row and Col can be hidden

Answer 4

The following will produce your results but the array formula will impact calculation lag depending upon the number of rows and column of data in the original data matrix.

    

The array formula¹ in A10 is,

=IFERROR(INDEX(A$2:A$6, MATCH(0, IF(COUNTIF(A$9:A9, A$2:A$6&"")<COUNT($1:$1), 0, 1), 0)), "")

The standard formula in B10 is,

=IF(LEN(A10), INDEX($B$1:INDEX($1:$1, MATCH(1E+99,$1:$1 )), , COUNTIF(A$10:A10, A10)), "")

Data retrieval in C10 is accomplished with,

=INDEX(A:J,MATCH(A10,A:A,0),MATCH(B10,$1:$1,0))

Fill down as necessary.

---

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.}