Using VLOOKUP to pull values into another sheet then count

**dbrewerton** · 01-08-2021, 01:53 PM

Greetings experts, I have a particular situation I could really use your help with. I'm open to formulas or even VBA but I need help. I'm completely stumped. What I'm wanting to do is to take an exported CSV of all values from a pivot table. The columns are as follows:

Vulnerabilty | AppName | High | Medium | Low

The application name is appended either by inactive or decommissioned. What I'm hoping to do is take the vulnerabilities and applications under it that do not have those words contained in the appname cell. So for reference, here's a few rows of data in the format.

example_before.png

When I make my actual count, I want to have the Vulnerability count to be equal to the total that is not inactive or decommissioned, i.e. 6 instead of 59 which includes the two apps I don't want.

So my new line in a separate sheet would look like this:

example_after.png

In the interest of saving space, the application column can be hidden or go away completely.

So any thoughts how this can be done? Thank you!

**BadlySpelledBuoy** · 01-09-2021, 09:57 AM

Hi and welcome.

I have a feeling that VBA could do what you need here, but to be sure it would be helpful to see a dummy workbook (not a picture of one) that has a little more data in it that shows what the initial data looks like and what your desired result looks like.

BSB

**dbrewerton** · 01-26-2021, 10:41 AM

Ok, let me put something together that will give you a bit more data. I'll be back and thank you!

**dbrewerton** · 01-26-2021, 10:54 AM

Ok, here is the sample workbook.

**dbrewerton** · 01-26-2021, 10:55 AM

Just to clarify, I am looking to remove any that are Decommissioned, only counting the High/Med/Low of active and inactive applications by vulnerability.

**dbrewerton** · 01-28-2021, 05:29 PM

Originally Posted by BadlySpelledBuoy

Hi and welcome.

I have a feeling that VBA could do what you need here, but to be sure it would be helpful to see a dummy workbook (not a picture of one) that has a little more data in it that shows what the initial data looks like and what your desired result looks like.

BSB

Did you come up with anything for creating a VBA for this? Also, I only need to strip out decommissioned apps, so the inactive and active ones stay. Still would like to get this so once the decom are removed, have it re-total by vulnerability.

**Glenn Kennedy** · 01-29-2021, 09:57 AM

Here is a formula that does what you want (I think...). Next time, please EXPLICITLY show your expected results, to remove guesswork.

To return the categories (copied down):
=IFERROR(INDEX(Raw!A:A,AGGREGATE(15,6,ROW(Raw!$A$2:$A$1000)/(Raw!$A$2:$A$1000<>""),ROWS(A$2:A2))),"")

To return the sum of valid results (copied across and down):
=IF($A2="","",SUMPRODUCT((LOOKUP(ROW(Raw!$A$2:$A$100),ROW(Raw!$A$2:$A$100)/(Raw!$A$2:$A$100<>""),Raw!$A$2:$A$100)=$A2)*(Raw!$B$2:$B$100<>"")*(ISERROR(SEARCH("-",Raw!$B$2:$B$100))*Raw!C$2:C$100)))

see the file.

**dbrewerton** · 01-29-2021, 11:17 AM

Beautiful! Thank you so much!!!

**Glenn Kennedy** · 01-29-2021, 11:18 AM

You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

**dbrewerton** · 01-29-2021, 12:09 PM

Ok, now what if I want to exclude any applications that have " - Decommissioned" in the application name? Would I modify:

=IF($A2="","",SUMPRODUCT((LOOKUP(ROW(Raw!$A$2:$A$100),ROW(Raw!$A$2:$A$100)/(Raw!$A$2:$A$100<>""),Raw!$A$2:$A$100)=$A2)*(Raw!$B$2:$B$100<>"")*(ISERROR(SEARCH("-",Raw!$B$2:$B$100))*Raw!C$2:C$100)))

to read like this?

=IF($A2="","",SUMPRODUCT((LOOKUP(ROW(Raw!$A$2:$A$100),ROW(Raw!$A$2:$A$100)/(Raw!$A$2:$A$100<>""),Raw!$A$2:$A$100)=$A2)*(Raw!$B$2:$B$100<>"")*(ISERROR(SEARCH(" - Decommissioned",Raw!$B$2:$B$100))*Raw!C$2:C$100)))

**Glenn Kennedy** · 01-29-2021, 12:15 PM

No....

=IF($A2="","",SUMPRODUCT((LOOKUP(ROW(Raw!$A$2:$A$100),ROW(Raw!$A$2:$A$100)/(Raw!$A$2:$A$100<>""),Raw!$A$2:$A$100)=$A2)*(Raw!$B$2:$B$100<>"")*(ISERROR(SEARCH("-",Raw!$B$2:$B$100))*Raw!C$2:C$100)))

Red: it looks in column B for a hyphen. If it DOES NOT find a hyphen, it returns an error.

Cyan: where it finds an error, this returns TRUE... and only those values (ones which have NO hyphen) go on into the rest of the formula. So...

XXX - inactive
XXX - decommissioned
XXX - whatever

will ALL be excluded.

Clear? is that what you intended?

You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

Using VLOOKUP to pull values into another sheet then count

LinkBack

Thread Tools

Rate This Thread

Display

Using VLOOKUP to pull values into another sheet then count

Re: Using VLOOKUP to pull values into another sheet then count

Re: Using VLOOKUP to pull values into another sheet then count

Re: Using VLOOKUP to pull values into another sheet then count

Re: Using VLOOKUP to pull values into another sheet then count

Re: Using VLOOKUP to pull values into another sheet then count

Re: Using VLOOKUP to pull values into another sheet then count

Re: Using VLOOKUP to pull values into another sheet then count

Re: Using VLOOKUP to pull values into another sheet then count

Re: Using VLOOKUP to pull values into another sheet then count

Re: Using VLOOKUP to pull values into another sheet then count

Thread Information

Users Browsing this Thread

Similar Threads

Pull Column Values from Another Sheet that are in the Current Sheet

[SOLVED] VLOOKUP to pull in duplicate values

[SOLVED] Vlookup to pull values from 3 worksheets.

[SOLVED] Pull data (last 100 days) from one sheet to another and transpose it (OFFSET & COUNT??)

How to pull vlookup or pull up multiple values from 2nd sheet- Need help fast.

Use vlookup or formula to pull multiple values

Need to pull multiple values from 1 workbook to another using vlookup?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions