Make automatic historic data output

[febausa]

Please recommend the appropriate excel formula.

Data output (new cell contains data output). Hand made for example

Data output will make automatic with excel, maybe with formula index

 

[Hui]

Firstly, Your sample data has an error


The highlighted 7 shouldn't be there

Next Note that I have simply used the frequency numbers in Column A


Because I am going to refer to them in the formulas

If you simply want to pickup the position of each Frequency in Row 4
ie: Column B is 1, Column C is 2 etc
B12: =IFERROR(LARGE((COLUMN($B$4:$BB$4)-1)*($B$4:$BB$4=$A11),COUNTIF($B$4:$BB$4,$A11)-COLUMNS($B$1:B$1)+1),"") Ctrl+Shift+Enter
Then copy across

If you want to retrieve the values from Row 5 then in
B12: =IFERROR(INDEX($B$5:$BB$5,,LARGE((COLUMN($B$4:$BB$4)-1)*($B$4:$BB$4=$A11),COUNTIF($B$4:$BB$4,$A11)-COLUMNS($B$1:B$1)+1)),"") Ctrl+Shift+EnterThen copy across

You can then copy these formulas to the rows below
eg Rows 14, 16 & 18

The difference between the two equations above is shown as:


Note
Row 12 Returns the position Number of the matching Freq.
Row 11 Returns the Value from Row 4 corresponding to the position Number of the matching Freq.

or see the attached file

 

[febausa]

Firstly, Your sample data has an error
View attachment 45519

The highlighted 7 shouldn't be there

Next Note that I have simply used the frequency numbers in Column A
View attachment 45520

Because I am going to refer to them in the formulas

If you simply want to pickup the position of each Frequency in Row 4
ie: Column B is 1, Column C is 2 etc
B12: =IFERROR(LARGE((COLUMN($B$4:$BB$4)-1)*($B$4:$BB$4=$A11),COUNTIF($B$4:$BB$4,$A11)-COLUMNS($B$1:B$1)+1),"") Ctrl+Shift+Enter
Then copy across

If you want to retrieve the values from Row 5 then in
B12: =IFERROR(INDEX($B$5:$BB$5,,LARGE((COLUMN($B$4:$BB$4)-1)*($B$4:$BB$4=$A11),COUNTIF($B$4:$BB$4,$A11)-COLUMNS($B$1:B$1)+1)),"") Ctrl+Shift+EnterThen copy across

You can then copy these formulas to the rows below
eg Rows 14, 16 & 18

The difference between the two equations above is shown as:
View attachment 45521

Note
Row 12 Returns the position Number of the matching Freq.
Row 11 Returns the Value from Row 4 corresponding to the position Number of the matching Freq.

or see the attached file

Hi Hui:

You are right, excuse my mistake.

Thank you for your help.

febausa

 

[febausa]

Hi Hui:

You are right, excuse my mistake.

Thank you for your help.

febausa

Hi Hui:

Please see attach file (example1.xlsx) and its text.

I tried to apply its formula in cell J51(color yellow) & J52(color blue) but there is some error that prevents applying alt + ctrl + enter. Please check my bad application in your formula to detect errors and in this way repair the formula


J51: = IFERROR(LARGE((COLUMN($J$48:$BJ$48)-1)*($J$48:$BJ$48=$H50),COUNTIF($J$48:$BJ$48,$H50)-COLUMNS($J$47:J$47)+1),"")

J52: =IFERROR(INDEX($J$49:$BJ$49,,LARGE((COLUMN($J$48:$BJ$48)-1)*($J$48:$BJ$48=$H50),COUNTIF($J$48:$BJ$48,$H50)-COLUMNS($J$47:J$47)+1)),"")

 

[NARAYANK991]

Hi ,

Both the following formulae , give an output of 11 ; there is no problem in entering them using CTRL SHIFT ENTER.

J51 : =IFERROR(LARGE((COLUMN($J$48:$BJ$48)-1)*($J$48:$BJ$48=$H50),COUNTIF($J$48:$BJ$48,$H50)-COLUMNS($J$47:J$47)+1),"")

J52 : =IFERROR(INDEX($J$49:$BJ$49,,LARGE((COLUMN($J$48:$BJ$48)-1)*($J$48:$BJ$48=$H50),COUNTIF($J$48:$BJ$48,$H50)-COLUMNS($J$47:J$47)+1)),"")

Narayan

 

[febausa]

Hi ,

Both the following formulae , give an output of 11 ; there is no problem in entering them using CTRL SHIFT ENTER.

J51 : =IFERROR(LARGE((COLUMN($J$48:$BJ$48)-1)*($J$48:$BJ$48=$H50),COUNTIF($J$48:$BJ$48,$H50)-COLUMNS($J$47:J$47)+1),"")

J52 : =IFERROR(INDEX($J$49:$BJ$49,,LARGE((COLUMN($J$48:$BJ$48)-1)*($J$48:$BJ$48=$H50),COUNTIF($J$48:$BJ$48,$H50)-COLUMNS($J$47:J$47)+1)),"")

Narayan

Thanks for your answer, but I use alt +ctrl + enter in cells:J51& J52 and no array formula and doing nothing. Maybe my excel have configuration problem,,,
Please, help me in this problem.

 

[NARAYANK991]

Hi ,

Sorry , but I have no idea ; in the English version of Excel , an array formula is entered by pressing the 3 keys combination of CTRL SHIFT ENTER.

See the attached file.

Narayan