Create a table from a subset of a range of columns

K

kwzahn

New Member
Joined
Feb 12, 2016
Messages
2
I'm new to MrExcel.com, first post.

I'm a VBA newbie. I'm tasked with creating a report of Client's with certain project statuses. I want to create this report on a separate sheet that already has some other data. The Advanced Filter almost does what I want, but it has two issues. I don't like that it hides the rows to create the filtered list. And it doesn't seem to update the filtered list when a Client status changes. Hoping that someone can help with some VBA to do this.

Trying to draw it out.. Want to only get project statuses that are >=1 and <=5B. And want the report to update automatically when a project status changes. I'd like to keep all the data out of the VBA as well, so someone can edit without getting into VBA.

Client Misc data Misc data2 Project Statusclient1 blah blah 0A-Project Not Opened; Not Expected
client2 blah blah 0B-Project Not Opened; Expected
client3 blah blah 1-Project Opened, but cert not started
client4 blah blah 2-Cert In Progress
client5 blah blah 3-Cert Complete
client6 blah blah 4-Production Prep (post-cert)
client7 blah blah 5A-Project complete/partially compliant
client8 blah blah 5B-Project complete/fully compliant
client9 blah blah 6 - Project canceled
client10 blah blah 0A-Project Not Opened; Not Expected
client11 blah blah 1-Project Opened, but cert not started


(On a different sheet)
Client Project Status
client3 1-Project Opened, but cert not started
client4 2-Cert In Progress
client5 3-Cert Complete
client6 4-Production Prep (post-cert)
client7 5A-Project complete/partially compliant
client8 5B-Project complete/fully compliant
client11 1-Project Opened, but cert not started


Thanks advance for the help!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Here is a non-vba solution:

Sheet 1:

Excel 2012
ABCDE
1ClientMisc DataMisc Data2ProjectStatus
21blahblah0AProject Not Opened; Not Expected
32blahblah0BProject Not Opened; Expected
43blahblah1Project Opened, but cert not started
54blahblah2Cert in Progress
65blahblah3Cert Complete
76blahblah4Production Prep (post-cert)
87blahblah5AProject complete/partially compliant
98blahblah5BProject complete/fully compliant
109blahblah6Project Canceled
1110blahblah0AProject Not Opened; Not Expected
1211blahblah1Project Opened, but cert not started
Sheet1
<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

Sheet 2:

Excel 2012
ABCDE
1ClientProjectStatus
210AProject Not Opened; Not Expected
320BProject Not Opened; Expected
431Project Opened, but cert not started
542Cert in Progress
653Cert Complete
764Production Prep (post-cert)
875AProject complete/partially compliant
985BProject complete/fully compliant
1096Project Canceled
11100AProject Not Opened; Not Expected
12111Project Opened, but cert not started
Sheet1
<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2:C2</th><td style="text-align:left">{=VLOOKUP(<font color="Blue">A2,Sheet1!A:E,{4,5},FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3:C3</th><td style="text-align:left">{=VLOOKUP(<font color="Blue">A3,Sheet1!A:E,{4,5},FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B4:C4</th><td style="text-align:left">{=VLOOKUP(<font color="Blue">A4,Sheet1!A:E,{4,5},FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B5:C5</th><td style="text-align:left">{=VLOOKUP(<font color="Blue">A5,Sheet1!A:E,{4,5},FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B6:C6</th><td style="text-align:left">{=VLOOKUP(<font color="Blue">A6,Sheet1!A:E,{4,5},FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B7:C7</th><td style="text-align:left">{=VLOOKUP(<font color="Blue">A7,Sheet1!A:E,{4,5},FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B8:C8</th><td style="text-align:left">{=VLOOKUP(<font color="Blue">A8,Sheet1!A:E,{4,5},FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B9:C9</th><td style="text-align:left">{=VLOOKUP(<font color="Blue">A9,Sheet1!A:E,{4,5},FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B10:C10</th><td style="text-align:left">{=VLOOKUP(<font color="Blue">A10,Sheet1!A:E,{4,5},FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B11:C11</th><td style="text-align:left">{=VLOOKUP(<font color="Blue">A11,Sheet1!A:E,{4,5},FALSE</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B12:C12</th><td style="text-align:left">{=VLOOKUP(<font color="Blue">A12,Sheet1!A:E,{4,5},FALSE</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.</td></tr></table><br />
 
Upvote 0
The only problem with that solution is that it pulls every row. I want to only pull the rows that have a Project Status of 1, 2, 3, 4, 5A, 5B.
 
Upvote 0
Here is a macro to do it:

Sheet 1 setup:

Excel 2012
ABCDEF
1ClientMisc DataMisc Data2ProjectStatus
21blahblah0AProject Not Opened; Not Expected
32blahblah0BProject Not Opened; Expected
43blahblah1Project Opened, but cert not started
54blahblah2Cert in Progress
65blahblah3Cert Complete
76blahblah4Production Prep (post-cert)
87blahblah5AProject complete/partially compliant
98blahblah5BProject complete/fully compliant
109blahblah6Project Canceled
1110blahblah0AProject Not Opened; Not Expected
1211blahblah1Project Opened, but cert not started
Sheet1
<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

Sheet 2 before macro runs:

Excel 2012
ABCD
1ClientProjectStatus
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sheet1
<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br />

Sheet 2 after macro runs:

Excel 2012
ABCD
1ClientProjectStatus
231Project Opened, but cert not started
342Cert in Progress
453Cert Complete
564Production Prep (post-cert)
675AProject complete/partially compliant
785BProject complete/fully compliant
8111Project Opened, but cert not started
9
10
Sheet1
<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br />

macro:
Code: 
Sub Get_Project_Status()
Dim lastRow As Long, nlastRow As Long
Dim rng As Range
lastRow = Range("D2").End(xlDown).Row

For r = 2 To lastRow
If Range("D" & r).Value = 1 Then
    Sheets("Sheet2").Range("A" & r).Value = Sheets("Sheet1").Range("A" & r).Value
    Sheets("Sheet2").Range("B" & r).Value = Sheets("Sheet1").Range("D" & r).Value
    Sheets("Sheet2").Range("C" & r).Value = Sheets("Sheet1").Range("E" & r).Value
ElseIf Range("D" & r).Value = 2 Then
    Sheets("Sheet2").Range("A" & r).Value = Sheets("Sheet1").Range("A" & r).Value
    Sheets("Sheet2").Range("B" & r).Value = Sheets("Sheet1").Range("D" & r).Value
    Sheets("Sheet2").Range("C" & r).Value = Sheets("Sheet1").Range("E" & r).Value
ElseIf Range("D" & r).Value = 3 Then
    Sheets("Sheet2").Range("A" & r).Value = Sheets("Sheet1").Range("A" & r).Value
    Sheets("Sheet2").Range("B" & r).Value = Sheets("Sheet1").Range("D" & r).Value
    Sheets("Sheet2").Range("C" & r).Value = Sheets("Sheet1").Range("E" & r).Value
ElseIf Range("D" & r).Value = 4 Then
    Sheets("Sheet2").Range("A" & r).Value = Sheets("Sheet1").Range("A" & r).Value
    Sheets("Sheet2").Range("B" & r).Value = Sheets("Sheet1").Range("D" & r).Value
    Sheets("Sheet2").Range("C" & r).Value = Sheets("Sheet1").Range("E" & r).Value
ElseIf Range("D" & r).Value = 5 & "A" Then
    Sheets("Sheet2").Range("A" & r).Value = Sheets("Sheet1").Range("A" & r).Value
    Sheets("Sheet2").Range("B" & r).Value = Sheets("Sheet1").Range("D" & r).Value
    Sheets("Sheet2").Range("C" & r).Value = Sheets("Sheet1").Range("E" & r).Value
ElseIf Range("D" & r).Value = 5 & "B" Then
    Sheets("Sheet2").Range("A" & r).Value = Sheets("Sheet1").Range("A" & r).Value
    Sheets("Sheet2").Range("B" & r).Value = Sheets("Sheet1").Range("D" & r).Value
    Sheets("Sheet2").Range("C" & r).Value = Sheets("Sheet1").Range("E" & r).Value
End If
Next r

Sheets("Sheet2").Activate
nlastRow = Range("A1048576").End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(nlastRow, 3)).SpecialCells(xlCellTypeBlanks)
rng.Rows.Delete Shift:=xlShiftUp

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
VBA code to apply autofilter to table already filtered by date by user via treeview dropdown not working
Replies
0
Views
2K
KathyVBA
K
8
  • Question
VBA to copying a range of data from multiple sheets into a summary sheet (but with some complexity that is giving me a headache)!
Replies
3
Views
514
igold
igold
R
  • Question
Create (or filter) table containing subset of data from master table depending on user dropdown selections
Replies
1
Views
1K
ghrain22
G
J
  • Question
Get Data from one Table, Create new table with filtered Data, auto populate
Replies
2
Views
760
jtmeunier
J
R
  • Question
Filter column by Date Range produces no results
Replies
3
Views
784
rogerbowering
R

Forum statistics

Threads
1,215,200
Messages
6,123,601
Members
449,109
Latest member
Sebas8956

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top