Possible IF formula

Rachelpan

New Member
Joined
Jan 29, 2020
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
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.



book 1.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,486
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGH
1Deptcodetime cotme jbdollarsCodes of interest
21A105100A
31B1510200B
42A105100
52B1510200
63A105100
73B1510200
84A105100
94B1510200
105A105100
115B1510200
12     
13     
Sheet2
Cell Formulas
RangeFormula
A2:E13A2=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))),"")
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Rachelpan

New Member
Joined
Jan 29, 2020
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGH
1Deptcodetime cotme jbdollarsCodes of interest
21A105100A
31B1510200B
42A105100
52B1510200
63A105100
73B1510200
84A105100
94B1510200
105A105100
115B1510200
12     
13     
Sheet2
Cell Formulas
RangeFormula
A2:E13A2=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
ABCDEFGH
1Deptcodetime cotme jbdollarsCodes of interest
21A105100A
31B1510200B
42A105100
52B1510200
63A105100
73B1510200
84A105100
94B1510200
105A105100
115B1510200
12     
13     
Sheet2
Cell Formulas
RangeFormula
A2:E13A2=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,486
Office Version
  1. 365
Platform
  1. Windows
You are welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,361
Messages
5,571,726
Members
412,415
Latest member
NitinPanjwani
Top