Home > >

Anyone Good With Excell? (7)

07-06-2016 08:45 PM #1 ametzgus (Member)
Anyone Good With Excell?

So basically. I am using excell to black list my placements. I have minimal excell background and need a little help-I can't seem to find the right answer online anywhere (might just be how I am searching for it).

Basically I export the placements data to CSV, trim it down with the filter tool to anything with a profit below 0, trim it down more with a spend greater than or equal to 2x my offer payout. Now what I am having trouble with is:

How do I filter out all the data associated with the already blacklisted placements?

In the picture I attached I have all the placements already black listed on the right, and the raw data to the left. I want to remove from the data all the already blacklisted placements. Anyone know how?

Click image for larger version.  Name: Screen Shot 2016-07-06 at 1.21.02 PM.png  Views: 115  Size: 170.7 KB  ID: 12012

07-06-2016 09:13 PM #2 tpm767 (Member)

Put the full list of blacklisted placements in column A (rows 1-36) on the second tab in the excel book. In column B put the number 1 next to each cell with a blacklisted placement ID. Get rid of the blacklisted placements on the first tab.

Make sure the sheet names are default for the formula I give you to work "Sheet1" and "Sheet2".

On the first tab (with all of the raw data) in column J (row 2) set up a formula that will "vlookup" off the placement value off of the second tab - also name colum J "Lookup" or something similar. Make sure to absolute reference the lookup range. It should look something like this: "=VLOOKUP(A2,Sheet2!$A$1:$B$36,2,FALSE)". Drag this down to the bottom of your raw data. NOTE: IF COLUMN B ON SHEET 1 IS THE PLACEMENTS AND NOT A, CHANGE A2 TO B2.

In column J, any blacklisted placements will show a "1", any non-blacklisted placements should show an "N/A". Once you have this down your list of placements, filter at the top on the "Lookup" (Column J) for any "1" (blacklisted placements), and select all of these filtered rows and delete them ( or Ctrl+- ). Once you unfilter you should be left with all "nonblacklisted placements" or the "N/A's".

Hope this doesn't sound like jibberish... let me know if that doesn't work.

Happy to trade excel skills for AM knowledge

07-16-2016 04:49 PM #3 cmdeal (Veteran Member)

Lookup and pivots tables in Excel are your best friends.

07-16-2016 06:39 PM #4 tpm767 (Member)

I've only been doing AM for about a month, but never thought Excel skills would come in handy as much as they have already.

07-19-2016 04:31 PM #5 nickpeplow (AMC Alumnus)

Data Tab > Filters

Click image for larger version.  Name: filtered-data.png  Views: 73  Size: 75.8 KB  ID: 12116

07-22-2016 07:48 AM #6 bobliu (Member)

Quote Originally Posted by nickpeplow View Post
Data Tab > Filters

Click image for larger version.  Name: filtered-data.png  Views: 73  Size: 75.8 KB  ID: 12116
Seconded, can't tell you how many times I use this feature.

07-22-2016 01:28 PM #7 that_brown_guy (Member)

I registered for this - https://courses.edx.org/courses/cour...e0180af457a4e/

will be useful

Home > >