Possible IF formula

[Rachelpan]

I have 5 columns of data in sheet 1.
Column A contains dept
Column B contains codes
Column C D E contain numerical data

I would like to know how I can auto extract only certain codes from each department along with their corresponding numerical data - and have it auto fill similar columns in sheet 2.
For purposes of this question I have put the sheet 2 column headings also in sheet 1.

For example I would like to only export the codes H and W - with dept and corresponding data-from each department and have only that placed into the other columns - leaving no blank cells in between. I have provided what it looks like on the sheet.

And would like this to be automatic every time new data is put into sheet 1.

 

[Peter_SSs]

I would prefer a formula that does the job.

In that case try something like this on Sheet2. Codes of interest are listed in column H.

The formula in A2 is copied across to column E and down as far as you might ever need after first adjusting (if required) the $10000 figure that I have used to ensure the range is big enough to cover any potential amount of data that could occur in your Sheet1. You mentioned possibly 50 departments with 100 or more codes per department which might give around 5000 rows so I allowed about double just in case.

Simply change the codes in column H and the results should automatically update.

Rachelpan.xlsm
	A	B	C	D	E	F	G	H
1	Dept	code	time co	tme jb	dollars			Codes of interest
2	1	A	10	5	100			A
3	1	B	15	10	200			B
4	2	A	10	5	100
5	2	B	15	10	200
6	3	A	10	5	100
7	3	B	15	10	200
8	4	A	10	5	100
9	4	B	15	10	200
10	5	A	10	5	100
11	5	B	15	10	200
12
13
Sheet2

Cell Formulas
Range	Formula
A2:E13	A2	=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!A$1:A$10000)/ISNUMBER(MATCH(Sheet1!$B$1:$B$10000,$H:$H,0)),ROWS(A$2:A2))),"")

 

[Rachelpan]

In that case try something like this on Sheet2. Codes of interest are listed in column H.

The formula in A2 is copied across to column E and down as far as you might ever need after first adjusting (if required) the $10000 figure that I have used to ensure the range is big enough to cover any potential amount of data that could occur in your Sheet1. You mentioned possibly 50 departments with 100 or more codes per department which might give around 5000 rows so I allowed about double just in case.

Simply change the codes in column H and the results should automatically update.

Rachelpan.xlsm
	A	B	C	D	E	F	G	H
1	Dept	code	time co	tme jb	dollars			Codes of interest
2	1	A	10	5	100			A
3	1	B	15	10	200			B
4	2	A	10	5	100
5	2	B	15	10	200
6	3	A	10	5	100
7	3	B	15	10	200
8	4	A	10	5	100
9	4	B	15	10	200
10	5	A	10	5	100
11	5	B	15	10	200
12
13
Sheet2

Cell Formulas
Range	Formula
A2:E13	A2	=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!A$1:A$10000)/ISNUMBER(MATCH(Sheet1!$B$1:$B$10000,$H:$H,0)),ROWS(A$2:A2))),"")

In that case try something like this on Sheet2. Codes of interest are listed in column H.

The formula in A2 is copied across to column E and down as far as you might ever need after first adjusting (if required) the $10000 figure that I have used to ensure the range is big enough to cover any potential amount of data that could occur in your Sheet1. You mentioned possibly 50 departments with 100 or more codes per department which might give around 5000 rows so I allowed about double just in case.

Simply change the codes in column H and the results should automatically update.

Rachelpan.xlsm
	A	B	C	D	E	F	G	H
1	Dept	code	time co	tme jb	dollars			Codes of interest
2	1	A	10	5	100			A
3	1	B	15	10	200			B
4	2	A	10	5	100
5	2	B	15	10	200
6	3	A	10	5	100
7	3	B	15	10	200
8	4	A	10	5	100
9	4	B	15	10	200
10	5	A	10	5	100
11	5	B	15	10	200
12
13
Sheet2

Cell Formulas
Range	Formula
A2:E13	A2	=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!A$1:A$10000)/ISNUMBER(MATCH(Sheet1!$B$1:$B$10000,$H:$H,0)),ROWS(A$2:A2))),"")

Peter
This was SUPERB!
Many Thanks!!

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.