Arrange of Data

[sambit]

sir,
Please help to resolve the issue. The example file is attached for your kind reference.

 

[AlanSidman]

See attached using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EmployeeCode", Int64.Type}, {"EmployeeName", type text}, {"9/1/2020", type text}, {"9/2/2020", type text}, {"9/3/2020", type text}, {"9/4/2020", type text}, {"9/5/2020", type text}, {"9/6/2020", type text}, {"9/7/2020", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"EmployeeCode", "EmployeeName"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

 

[Peter Bartholomew]

I have the good fortune to have access to Charles William's FastExcel so, for me, the solution is

= UNPIVOT(Table,dates,headers,"Date","Output",1)

Since that would not help many, I note that Excel 365 offers the means to generate an index for the output table and the LET function to organise the calculation.

= LET(
  n, COUNTA(input),
  k, SEQUENCE(n, 1, 0),
  dateIndex, 1+MOD(k, COUNT(dates)),
  emplIndex, 1+QUOTIENT(k, COUNT(dates)),
  INDEX(input, emplIndex, dateIndex) )

Without Excel 365, it is still possible but more trouble than it is worth given that you have access to standard functionality within PowerQuery.

 

[vletm]

sambit
for cell O4 =INDEX(D$4:J$6,MATCH(L4,B$4:B$6,0),MATCH(N4,D$3:J$3,0)) and copy down

 

[sambit]

Vletm Sir,
Excellent Solution.
Thank you very much.

 

[Peter Bartholomew]

@vletm Do you see the Emp.Code and Date columns as being manually generated?

 

[vletm]

Peter Bartholomew
Yes and one column values would like to solve.

 

[Peter Bartholomew]

Probably something I should have explained was that the LET formula I posted doesn't just apply to the output column; with minor modifications it returns each of the four columns.

The employee code and name could be returned by
  INDEX(employee, emplIndex, {1,2})

and the dates by
  INDEX(dates, dateIndex)
 
The output is as shown above
  INDEX(input, emplIndex, dateIndex)

Match was not required because the indices had already been calculated.

To implement the formula for a version of Excel other than 365 requires the variables to be replaced by their formula, creating nested formulas. Unfortunately, to replace SEQUENCE, requires a somewhat unsatisfactory ROW/INDIRECT formula. I am more interested in going in the opposite direction to produce a formula

= LET(
  n, COUNTA(input),
  k, SEQUENCE(n,1,0),
  dateIndex, 1+MOD(k, COUNT(dates)),
  emplIndex, 1+QUOTIENT(k, COUNT(dates)),
  code, INDEX(EmployeeCode, emplIndex),
  name, INDEX(EmployeeName, emplIndex),
  date, INDEX(dates, dateIndex),
  output, INDEX(input, emplIndex, dateIndex),
  CHOOSE({1,2,3,4}, code, name, date, output) )

which will, when placed in cell L4, generate an entire table of results which adjusts dynamically as the input is changed.

Sadly, such approaches will not become the norm anytime soon .

 

[Peter Bartholomew]

I would be interested to know whether the attached workbook functions with traditional Excel. I have moved the intermediate row and column calculations out of the LET functions and into Named Formulas. I have also reversed my normal strategy of building array formula wherever possible and reverted to single-cell formulas copied down.

 

[vletm]

for Peter Bartholomew

 

[Peter Bartholomew]

@vletm
Thank you, that is what I hoped for. The #NAME? error on the third table would be due to the use of the LET and SEQUENCE functions.

 

[vletm]

for Peter Bartholomew
Hoped for?
The original expected result could solve 'shorter way' too
- to solve once A, G and B, but I confirm that if eg all A's are 'different' A then it would work too.
Isn't 'Your way', a middle table, basically same index-formula as I used.
... You have solved those rows and columns 'Your way'
... which need more time than with 'traditional Excel' then ... hmm?
You've comment many times 'faster is better' -- Total time matters too - calculation time is only to get results.
Of course, many things could solve many ways ... 'Your way' too.
#NAME? ... I would solve those 'my way', but this time I tried to be patient.
Keep on using 'future-versions' - I'll read those always too ... but I cannot use those.

 

[John Jairo V]

Hi to all!

One option with traditional formulas in the attached file. Blessings!

 

[Peter Bartholomew]

@vletm
"My way": I am interested in exploring the opportunities provided by the new versions of Excel; whether there is anything achieved by publishing them here when most members do not have access to such methods, is another question. As for the future, the suggestion seems to be "If LET came as a surprise, you ain't seen nothing yet!" For years, my objectives have been 'abnormal' for a spreadsheet developer. I have taken part in competing for the shortest formula but, in reality, I value readability before brevity. I use array formulas to in preference to copied formulas to simplify the overall logic. I am willing to sacrifice some performance for better semantics (but not too much).

@John Jairo V
That raises the bar! A remarkable set of formulas!
The logic I used, was not strictly that of unpivoting a dataset (as per @AlanSidman's PowerQuery solution). I was thinking in terms of moving between 1 & 2D representations of a two dimensional array and, in that context, null fields are as valid as any other value, The simplest change to my LET formula, is to allow the calculation to proceed as before but to filter the list before returning it to the workbook.

= LET(
  n, COUNT(dates)*COUNT(EmployeeCode),
  k, SEQUENCE(n,1,0),
  dateIndex, 1+MOD(k, COUNT(dates)),
  emplIndex, 1+QUOTIENT(k, COUNT(dates)),
  code, INDEX(EmployeeCode, emplIndex),
  name, INDEX(EmployeeName, emplIndex),
  date, INDEX(dates, dateIndex),
  output, INDEX(input, emplIndex, dateIndex),
  table, CHOOSE({1,2,3,4}, code, name, date, output),
  FILTER(table, output<>"") )

Seems that this question has thrown up some interesting challenges.

 

[bosco_yip]

Another traditional formulas option, all formulas basically using INDEX function as per following.

Code/Name [L4] copied to [M4] and all down :
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($4:$6)/($D$4:$J$6<>""),ROW(A1))),"")

Date [N4] :
=IFERROR(INDEX($3:$3,AGGREGATE(15,6,COLUMN(D:J)/(INDEX(D$4:J$6,MATCH(L4,B$4:B$6,0),0)<>""),COUNTIF(L$4:L4,L4))),"")

Output [O4] :
=IFERROR(INDEX($D$4:$J$6,MATCH(L4,B$4:B$6,0),MATCH(N4,D$3:J$3,0)),"")