Automatically Generate A List Based on Certain Conditions - HELP

[manona]

Hi,
I have a question in regards to automatic lists in Excel and will try my best to explain it clearly. Any help is appreciated.

Very simplified, I have 3 tabs. #1 has raw data, #2 has a list of "cost centers" and #3 is a tab in which I would like to have an automatic list of all rows with the cost centers not included in #2.

For example:

Tab #1 would look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Region[/TD]
[TD]Cost Center[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Alberta[/TD]
[TD]1111[/TD]
[TD]5$[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Ontario[/TD]
[TD]2222[/TD]
[TD]2$[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Ontario[/TD]
[TD]2222[/TD]
[TD]8$[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Quebec[/TD]
[TD]3333[/TD]
[TD]5$[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Quebec[/TD]
[TD]3333[/TD]
[TD]2$[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Quebec[/TD]
[TD]3333[/TD]
[TD]8$[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Manitoba[/TD]
[TD]5555[/TD]
[TD]10$[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Nova Scotia[/TD]
[TD]6666[/TD]
[TD]3$[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Saskatchewan[/TD]
[TD]4444[/TD]
[TD]2$[/TD]
[/TR]
</tbody>[/TABLE]

Tab #2 could look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Region[/TD]
[TD]Cost Center[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Alberta[/TD]
[TD]1111[/TD]
[TD]5$[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Ontario[/TD]
[TD]2222[/TD]
[TD]10$[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Quebec[/TD]
[TD]3333[/TD]
[TD]15$[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Saskatchewan[/TD]
[TD]4444[/TD]
[TD]2$[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Nova Scotia[/TD]
[TD]6666[/TD]
[TD]3$[/TD]
[/TR]
</tbody>[/TABLE]

I would like tab #3 to automatically list the entire row of any line items from tab #1 that were not included in tab#2, using the cost center as reference. It would generate this info in this case:

[TABLE="width: 500"]
<tbody>[TR]
[TD]2015[/TD]
[TD]Manitoba[/TD]
[TD]5555[/TD]
[TD]10$[/TD]
[/TR]
</tbody>[/TABLE]

If 5555 was included in tab #2, tab#3 would then be blank. If 5555 and 6666 were not included in tab #2, all line items for 5555 and 6666 would automatically be listed in #3.

I have done this previously for another model, but I can't seem to adapt it or understand my logic haha.

If you could help, it would be so appreciated.

Thank you!!

Manon

 

[hiker95]

manona,

Welcome to the MrExcel forum.


Here is a macro solution for you to consider.

I assume that all worksheets, Tab #1, Tab #2, and, Tab #3, already exist, and, that they have titles in row 1.

You can change the worksheet names in the macro.

Sample raw data worksheets, Tab #1, and, Tab #2:

Excel 2007
	A	B	C	D
1	Year	Region	Cost Center	Amount
2	2015	Alberta	1111	$5
3	2015	Ontario	2222	$2
4	2015	Ontario	2222	$8
5	2015	Quebec	3333	$5
6	2015	Quebec	3333	$2
7	2015	Quebec	3333	$8
8	2015	Manitoba	5555	$10
9	2015	Nova Scotia	6666	$2
10	2015	Saskatchewan	4444	$2
11
Tab #1

Excel 2007
	A	B	C	D
1	Year	Region	Cost Center	Amount
2	2015	Alberta	1111	$5
3	2015	Ontario	2222	$10
4	2015	Quebec	3333	$15
5	2015	Saskatchewan	4444	$2
6	2015	Nova Scotia	6666	$3
7
Tab #2

And, the results in worksheet Tab #3:

Excel 2007
	A	B	C	D
1	Year	Region	Cost Center	Amount
2	2015	Manitoba	5555	$10
3
Tab #3

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub FindMissingCostCenters()
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet
Dim r As Range, c As Range, nr As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Tab #1")   '<-- you can change the sheet name here
Set w2 = Sheets("Tab #2")   '<-- you can change the sheet name here
Set w3 = Sheets("Tab #3")   '<-- you can change the sheet name here
With w1
  For Each r In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
    Set c = w2.Columns(3).Find(r.Value, LookAt:=xlWhole)
    If c Is Nothing Then
      nr = w3.Cells(w3.Rows.Count, "C").End(xlUp).Row + 1
      .Range("A" & r.Row & ":D" & r.Row).Copy w3.Range("A" & nr)
      Application.CutCopyMode = False
    End If
  Next r
End With
With w3
  .UsedRange.Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the FindMissingCostCenters macro.

 

[manona]

Thank you so much @hiker95, your help is really appreciated!

 

[hiker95]

manona,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.