Complete Missing Data

[darrenscrawford]

Hey Hey... Could use some help on this one.

I've got some web analytics data that is flawed. There are basically three columns

IP ADDRESS | CAMPAIGN NAME | USER NAME

As you can see from the sample: In Row 2, IP Address 1.2.3.4 came to the site from the Super Ad campaign but did not buy anything (no username).

They came back later in Row 3 (directly or from a non-campaign source like Google) and purchased which tags that IP address with the username.

The data for the month has more than 36,000 rows - one for each session. This represents about 27,000 unique IP addresses that visited.

How can I get this data to "fill in the blanks" so that I can match up users that purchased that have come from a campaign but did not purchase on the first session?

In other words, how do I complete the sheet to look more like this? Keep in mind that IP address 1.2.3.4 might have a handful of rows with only one of them showing the campaign they came in from.

I suspect using multiple columns to build out and then summarize?

Thanks in advance.
-Darren

 

[JackDanIce]

Do you ever have situations where you have the same IP address in column A but differing values in column B? If not, the easiest non-VBA method I can think of (because I don't want to work out the code right now) would be:

1) Sort your three columns, by column A

2) Use an advanced filter to filter for unique values in column A and copy these unique values into a new column (say D)

3) In E, enter the formula =VLOOKUP(D2,A:C,2,0) and drag down next to the last row in column D

4) In F, enter the formula = VLOOKUP(D2,A:C,3,0) and drag down next to the last row in column D

5) Copy and paste values of columns E and F (to remove the formula)

6) Delete all values in column B and then in B2 enter formula =VLOOKUP(A2,D:F,2,0) and down down to the cell next to the last row in column A

7) Delete all values in column C and in C2 enter formula =VLOOKUP(A2,D:F,3,0) and down down to the cell next to the last row in column A

8) Copy and paste values over columns B and C to remove formula

 

[darrenscrawford]

Thanks I will give that a try. Advanced filter before crashed Excel so I might have to do a reboot and free up some RAM. It doesn't like 36,000 rows for that.

I have not seen an IP ADDRESS with more than 1 campaign source, but that is entirely possible.