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]

Could you give us the sample data & expected results again with XL2BB so that we can copy/paste for testing without having to manually type a whole lot of data?

 

[Rachelpan]

Could you give us the sample data & expected results again with XL2BB so that we can copy/paste for testing without having to manually type a whole lot of data?

Data base is A-E
Expected results in I-M

Dept	code	time co	tme jb	dollars				Dept	code	time co	tme jb	dollars
1	A	10	5	100				1	H	20	15	300
1	B	15	10	200				1	W	40	35	700
1	H	20	15	300				2	H	20	15	300
1	I	25	20	400				2	W	40	35	700
1	N	30	25	500
1	T	35	30	600
1	W	40	35	700
2	A	10	5	100
2	B	15	10	200
2	H	20	15	300
2	I	25	20	400
2	N	30	25	500
2	T	35	30	600
2	W	40	35	700
3	A	10	5	100
3	B	15	10	200
3	H	20	15	300
3	I	25	20	400
3	N	30	25	500
3	T	35	30	600
3	W	40	35	700
4	A	10	5	100
4	B	15	10	200
4	H	20	15	300
4	I	25	20	400
4	N	30	25	500
4	T	35	30	600
4	W	40	35	700
5	A	10	5	100
5	B	15	10	200
5	H	20	15	300
5	I	25	20	400
5	N	30	25	500
5	T	35	30	600
5	W	40	35	700

 

[Peter_SSs]

Expected results in I-M

Your expected results do not include Dept 3, 4 or 5 but I assume those should be included too?

I also forgot to ask if you were concerned with the method of achieving the results as this could be done with vba (solution suggestion below), formulas, or Power Query?

Anyway, this is a method using vba. To implement ..
1. Right click the Sheet1 sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by entering or re-entering any data in Sheet1.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Columns("A:E").Copy Destination:=Sheets("Sheet2").Range("A1")
  With Sheets("Sheet2").UsedRange
    .AutoFilter Field:=2, Criteria1:="<>H", Operator:=xlAnd, Criteria2:="<>W"
    .Offset(1).EntireRow.Delete
    .Parent.AutoFilterMode = False
  End With
End Sub

 

[sandy666]

use a Pivot Table in Tabular Form with filters

 

[Peter_SSs]

use a Pivot Table in Tabular Form with filters

That will not account for ...

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

 

[sandy666]

why not?
add new data then refresh pivot
or
vba can be used for automatically refresh pivot

Private Sub Worksheet_Change(ByVal Target As Range)
  Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

 

[Peter_SSs]

why not?

Because you hadn't posted the code to do the refresh or even mentioned that such code would be required.
Now you have.

 

[sandy666]

this is a simple vba and I think easy to find, however I don't recommend autorefresh for much bigger data. it works well with a small data like in example
anyway it's user's choice

edit:
btw. I don't need edit any code if I want to move pivot somewhere or change the filter(s) on the same sheet

 

[Rachelpan]

Your expected results do not include Dept 3, 4 or 5 but I assume those should be included too?

I also forgot to ask if you were concerned with the method of achieving the results as this could be done with vba (solution suggestion below), formulas, or Power Query?

Anyway, this is a method using vba. To implement ..
1. Right click the Sheet1 sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by entering or re-entering any data in Sheet1.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Columns("A:E").Copy Destination:=Sheets("Sheet2").Range("A1")
  With Sheets("Sheet2").UsedRange
    .AutoFilter Field:=2, Criteria1:="<>H", Operator:=xlAnd, Criteria2:="<>W"
    .Offset(1).EntireRow.Delete
    .Parent.AutoFilterMode = False
  End With
End Sub

Peter
You are correct. All departments would be included.
I would prefer a formula that does the job.
The data base could potentially have 50 or more departments and possibly 100 or more repeated codes in each dept. (Example - dept 1 might have 30 H's 20 W's and multiples of the other codes as well in each department.

So I need a formula that I can change to pull - say - all N's and A's from all departments instead of H and W. And auto fill the other sheet.