Extract figures above a certain value and transpose data

[nadeem001]

Good day everyone

I need your urgent assistance...

I have a excel table that has headings in column A and row 1

In the remainder of cells (column b- column z and row 2 - 20) there are values

I need to automate my process with the following steps

1. Identify all cells with Absolute values greater than 100
2. Highlight cells that meet criteria in step 1
3. For each of the highlighted cells I need to create a new table that shows:
3.1 the heading from row 1 in column A
3.2 the heading from the original data column A in column B
3.3 the amount from the highlighted cell in column C

I know my explanation is alittle confusing but will appreciate any feedback, thank you

 

[Eric W]

Please update your account profile with the version of Excel you are using. The "best" solution often depends on what version you have, with its specific functions available. Also, are you looking for a formula, or a VBA solution?

Here's one possibility I came up with, if you have Excel 365 or Excel 2021:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		Banana	Cherry	Donut	Egg	Fig	Grape	Hot Dog		Column Headings	Row Headings	Value
2	Red				5					Cherry	Yellow	101
3	Orange	6	7				77			Banana	Blue	202
4	Yellow		101		66					Egg	Blue	-333
5	Green									Grape	Blue	1234
6	Blue	202			-333		1234			Donut	Violet	234
7	Indigo
8	Violet			234
9
Sheet1

Cell Formulas
Range	Formula
J2:L6	J2	=LET(nbr,SUMPRODUCT(--(ABS(B2:H8)>100)),arr,IF(ABS(B2:H8)>100,ROW(B2:H8)*100+COLUMN(B2:H8),""),vals,SMALL(arr,SEQUENCE(nbr)),r,INT(vals/100),c,MOD(vals,100),CHOOSE({1,2,3},INDEX(1:1,c),INDEX(A:A,r),INDEX(A1:H8,r,c)))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:H8	Expression	=ABS(B2)>100	text	NO

 

[nadeem001]

Thank You for your quick response -
Im 99% sure the version of Excel im running is Office 365 - What is the best way to confirm?

I unfortunately cant get the formula above to work it says there is a error with the let formula

Im indifferent if i use a formula or VBA but basically this is what i require:

Input data

Output Data:

Thank you again for your assistance

 

[Eric W]

To see what version you have, click on File > Account and it should tell you. As far as the error you're getting, what is it? #NAME, #VALUE, or something else? Or do you just get bad results? If so, what?

I've updated the formula so that it's easier to transport to another sheet. Also, so that it sorts by columns instead of rows:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		Banana	Cherry	Donut	Egg	Fig	Grape	Hot Dog		Column Headings	Row Headings	Value
2	Red				5			-888		Banana	Blue	202
3	Orange	6	7				77	-8		Cherry	Yellow	101
4	Yellow		101		66					Cherry	Green	5000
5	Green		5000							Donut	Violet	234
6	Blue	202			-333		1234			Egg	Blue	-333
7	Indigo									Grape	Blue	1234
8	Violet			234						Hot Dog	Red	-888
9
Sheet1

Cell Formulas
Range	Formula
J2:L8	J2	=LET(data,Sheet1!A1:H8,comp,IFERROR(--(ABS(data)>100),0),nbr,SUM(comp),arr,IF(comp,COLUMN(data)*100+ROW(data),""),vals,SMALL(arr,SEQUENCE(nbr)),r,MOD(vals,100)-MIN(ROW(data))+1,c,INT(vals/100)-MIN(COLUMN(data))+1,CHOOSE({1,2,3},INDEX(data,1,c),INDEX(data,r,1),INDEX(data,r,c)))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:H8	Expression	=ABS(B2)>100	text	NO

To move the formula to another sheet, just change the Sheet1!A1:H8 reference to the location of the table, Sheet1!A1:Z20 based on your first post. Note that if the bottom row of the table is past row 100, we'll need to tweak the formula a little.

 

[nadeem001]

Thank you for your response, I can confirm it is office 365:

I still cant get the formula to work - i get the following error:

Once again i am truly grateful for your responses and assistance

 

[Eric W]

Very peculiar. You can see that it works on my system. As far as I know, "data" is not a reserved word you can't use for a LET name. However, we can try changing it to something else to see if that helps:

=LET(MYdata,Sheet1!A1:H8,comp,IFERROR(--(ABS(MYdata)>100),0),nbr,SUM(comp),arr,IF(comp,COLUMN(MYdata)*100+ROW(MYdata),""),vals,SMALL(arr,SEQUENCE(nbr)),r,MOD(vals,100)-MIN(ROW(MYdata))+1,c,INT(vals/100)-MIN(COLUMN(MYdata))+1,CHOOSE({1,2,3},INDEX(MYdata,1,c),INDEX(MYdata,r,1),INDEX(MYdata,r,c)))

 

[nadeem001]

managed to fix the above error- it was just a format issue because my laptop uses colons instead of comma`s

however now im having a different issue, this is the result i am getting:

not sure what im doing wrong, also for columns K&L do i paste in the same formula?

 

[nadeem001]

Hey Eric

Managed to get the above to work with the following VBA:

Sub TBLines()
'
' TBLines Macro
'

'
Range("E51").Select
Sheets("TB").Select
Range("a2:b2", Range("a" & Rows.Count).End(xlUp)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
EmptyRowAccounts
ActiveSheet.Paste
End Sub
Sub DltCol()
'
' DltCol Macro
'

'
Range("F61").Select
Sheets("TB").Select
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
End Sub
Sub LoopProcess()

Range("E1").Select
Sheets("TB").Select
Range("b1").Select
Do Until ActiveCell.Value = ""
'REPLACE 100 WITH MATERIALITY

ActiveSheet.Range("$A$1:$I$8").AutoFilter Field:=2, Criteria1:=">=100", _
Operator:=xlOr, Criteria2:="<=-100"
Sheets("Summary").Select
Range("G1").Select
Sheets("TB").Select
Range("B1").Select
Selection.Copy
Sheets("Summary").Select
EmptyRow
ActiveSheet.Paste
TBLines
DltCol

Range("E1").Select
Sheets("TB").Select
Range("b1").Select
ActiveCell.Select

Loop
'
End Sub


Thank you for all your assistance - it is highly appreciated