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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,528
Office Version
  1. 365
Platform
  1. Windows
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

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

Deptcodetime cotme jbdollarsDeptcodetime cotme jbdollars
1A1051001H2015300
1B15102001W4035700
1H20153002H2015300
1I25204002W4035700
1N3025500
1T3530600
1W4035700
2A105100
2B1510200
2H2015300
2I2520400
2N3025500
2T3530600
2W4035700
3A105100
3B1510200
3H2015300
3I2520400
3N3025500
3T3530600
3W4035700
4A105100
4B1510200
4H2015300
4I2520400
4N3025500
4T3530600
4W4035700
5A105100
5B1510200
5H2015300
5I2520400
5N3025500
5T3530600
5W4035700
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,528
Office Version
  1. 365
Platform
  1. Windows
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).

VBA Code:
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

Well-known Member
Joined
Oct 24, 2015
Messages
7,454

ADVERTISEMENT

use a Pivot Table in Tabular Form with filters

pt.png
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,454

ADVERTISEMENT

why not?
add new data then refresh pivot
or
vba can be used for automatically refresh pivot
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh
End Sub
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,454
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 ;)
 
Last edited:

Rachelpan

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

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,236
Messages
5,576,896
Members
412,752
Latest member
LUIS SAMANO
Top