Excel Formula Help

[Anonnymouse]

Hey Good Afternoon,

I am stuck in the mud here.

I am trying to pull the row of information excluding column D and E based on the specific text on cell A4. I have used Index and match formula but keep running into a REF# error.

Any help on this would be appreciated.

Thank you.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

Sample Data

Formula Data

Order#

Date

No

Error Code

Old Price

New Price

User

Order#

Date

Old Price

New Price

User

Err_1944

512878

1/1/2023

1

Err_1944

200

300

Atlanta

512878​

1/1/2023​

200​

300​

Atlanta

512878

1/4/2023

2

Err_1832

100

105

Boston

971110​

1/8/2023​

900​

10​

Frank

654888

1/5/2023

3

Err_0782

400

200

Colton

428753​

1/10/2023​

899​

399​

Henry

654888

1/6/2023

4

Err_1480

600

800

Delta

971110

1/7/2023

5

Err_1832

50

100

Echo

971110

1/8/2023

6

Err_1944

900

10

Frank

971110

1/9/2023

7

Err_1480

1090

350

George

428753

1/10/2023

8

Err_1944

899

399

Henry

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Sample Data								Formula Data
2		Order#	Date	No	Error Code	Old Price	New Price	User		Order#	Date	Old Price	New Price	User
3	Err_1944	512878	01/01/2023	1	Err_1944	200	300	Atlanta		512878	01/01/2023	200	300	Atlanta
4		512878	01/04/2023	2	Err_1832	100	105	Boston		971110	01/08/2023	900	10	Frank
5		654888	01/05/2023	3	Err_0782	400	200	Colton		428753	01/10/2023	899	399	Henry
6		654888	01/06/2023	4	Err_1480	600	800	Delta
7		971110	01/07/2023	5	Err_1832	50	100	Echo
8		971110	01/08/2023	6	Err_1944	900	10	Frank
9		971110	01/09/2023	7	Err_1480	1090	350	George
10		428753	01/10/2023	8	Err_1944	899	399	Henry
11
Data

Cell Formulas
Range	Formula
J3:N5	J3	=FILTER(CHOOSECOLS(B3:H100,1,2,5,6,7),E3:E100=A3)
Dynamic array formulas.

 

[lrobbo314]

How about this?

Prcatice.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Sample Data								Formula Data
2		Order#	Date	No	Error Code	Old Price	New Price	User		Order#	Date	Old Price	New Price	User
3	Err_1944	512878	1/1/2023	1	Err_1944	200	300	Atlanta		3/17/3304	1/1/2023	200	300	Atlanta
4		512878	1/4/2023	2	Err_1832	100	105	Boston		10/21/4558	1/8/2023	900	10	Frank
5		654888	1/5/2023	3	Err_0782	400	200	Colton		428753	44936	899	399	Henry
6		654888	1/6/2023	4	Err_1480	600	800	Delta
7		971110	1/7/2023	5	Err_1832	50	100	Echo
8		971110	1/8/2023	6	Err_1944	900	10	Frank
9		971110	1/9/2023	7	Err_1480	1090	350	George
10		428753	######	8	Err_1944	899	399	Henry
Sheet5

Cell Formulas
Range	Formula
J2:N5	J2	=LET(cols,{1,2,5,6,7},d,CHOOSECOLS(FILTER(B3:H10,E3:E10=A3),cols),h,CHOOSECOLS(B2:H2,cols),VSTACK(h,d))
Dynamic array formulas.

 

[Anonnymouse]

=FILTER(CHOOSECOLS(B3:H100,1,2,5,6,7),E3:E100=A3)

Thank you Fluff, This worked perfectly.

I am wondering if i could add a filter in the formula that excludes a specific user? for example exclude atlanta

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Sample Data								Formula Data
2		Order#	Date	No	Error Code	Old Price	New Price	User		Order#	Date	Old Price	New Price	User
3	Err_1944	512878	01/01/2023	1	Err_1944	200	300	Atlanta	Atlanta	971110	01/08/2023	900	10	Frank
4		512878	01/04/2023	2	Err_1832	100	105	Boston		428753	01/10/2023	899	399	Henry
5		654888	01/05/2023	3	Err_0782	400	200	Colton
6		654888	01/06/2023	4	Err_1480	600	800	Delta
7		971110	01/07/2023	5	Err_1832	50	100	Echo
8		971110	01/08/2023	6	Err_1944	900	10	Frank
9		971110	01/09/2023	7	Err_1480	1090	350	George
10		428753	01/10/2023	8	Err_1944	899	399	Henry
11
Data

Cell Formulas
Range	Formula
J3:N4	J3	=FILTER(CHOOSECOLS(B3:H100,1,2,5,6,7),(E3:E100=A3)*(H3:H100<>I3))
Dynamic array formulas.

 

[Anonnymouse]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Sample Data								Formula Data
2		Order#	Date	No	Error Code	Old Price	New Price	User		Order#	Date	Old Price	New Price	User
3	Err_1944	512878	01/01/2023	1	Err_1944	200	300	Atlanta	Atlanta	971110	01/08/2023	900	10	Frank
4		512878	01/04/2023	2	Err_1832	100	105	Boston		428753	01/10/2023	899	399	Henry
5		654888	01/05/2023	3	Err_0782	400	200	Colton
6		654888	01/06/2023	4	Err_1480	600	800	Delta
7		971110	01/07/2023	5	Err_1832	50	100	Echo
8		971110	01/08/2023	6	Err_1944	900	10	Frank
9		971110	01/09/2023	7	Err_1480	1090	350	George
10		428753	01/10/2023	8	Err_1944	899	399	Henry
11
Data

Cell Formulas
Range	Formula
J3:N4	J3	=FILTER(CHOOSECOLS(B3:H100,1,2,5,6,7),(E3:E100=A3)*(H3:H100<>I3))
Dynamic array formulas.

[/RANGE

Cell Formulas
Range	Formula
Dynamic array formulas.

Thank you Fluff. Sorry this is my last question I promise.. Could I also add how many times the order number is showing up on my list as so order 971110. Thank you.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

Sample Data

Formula Data

Order#

Date

No

Error Code

Old Price

New Price

User

Order#

Date

Old Price

New Price

User

Number of Times

512878​

1/1/2023​

1​

Err_1944

200​

300​

Atlanta

Atlanta

971110​

1/8/2023​

900​

10​

Frank

2​

512878​

1/4/2023​

2​

Err_1832

100​

105​

Boston

428753​

1/10/2023​

899​

399​

Henry

1​

Err_1944

654888​

1/5/2023​

3​

Err_0782

400​

200​

Colton

971110​

1/9/2023​

1000​

500​

Frank

2​

654888​

1/6/2023​

4​

Err_1480

600​

800​

Delta

971110​

1/7/2023​

5​

Err_1832

50​

100​

Echo

971110​

1/8/2023​

6​

Err_1944

900​

10​

Frank

971110​

1/9/2023​

7​

Err_1480

1090​

350​

George

428753​

1/10/2023​

8​

Err_1944

899​

399​

Henry

971110​

1/9/2023​

6​

Err_1944

1000​

500​

Frank

 

[Fluff]

As this is now a significantly different question, it needs a new thread. Thanks

 

[Anonnymouse]

As this is now a significantly different question, it needs a new thread. Thanks

Ok I will make a new thread thank you!

 

[Fluff]

You're welcome & thanks for the feedback.