# Align and Compare stats help please

1. ## Align and Compare stats help please

Hello.

I've have a couple of statistics reports from two different sources for mainly the same sites. However, since some of the sites might not have had any traffic for that time period, the rows are off and I just can't figure a quick way to match and align the stats. I also have a "Percentage Change" column at the end that I would need as a percent increase or decrease to compare the revenue difference between the various sources. Can someone help me and give me step by step instructions on how to align the matching "Site Names" along with their other info onto the same rows. Again, I am lost with excel and step by step instructions to set this up would be appreciated. I attached a sample. Thank you.

2. ## Re: Align and Compare stats help please

Here's one way you could do it:

I plopped the second report somewhere else and then used a VLOOKUP to find and retrieve the information.

=VLOOKUP(lookup what, where, what column, [0 for exact match])

In my example, I'm searching for the site in Column A, in A21 to D24 (vlookup only matches left-most column), and returning column 2, 3, and 4.

Attachment 260838

3. ## Re: Align and Compare stats help please

Thanks Daffodil11, but that is not correct. For example I need "Site Names" (A & F) along with their other columns (B,C, D & G,H,I) to align/sort on the same row for each matching "Site Name". I do not want to combine the revenues etc.. but just show them side by side. The sample I attached is a small list from hundreds of "Site Names" that I need to match and align. Someone mentioned "Pivot" but I'm not sure if that will help or to even get a formula or the steps to get this sorted. I need help.

Help Anyone?

5. ## Re: Align and Compare stats help please

How would your outcome look like?

6. ## Re: Align and Compare stats help please

Hi RobertMika,

I attached a samplefile outcome I think should work. Basicially, I need to match and align all the items on column A along with it's following stats to the items on column F along with it's following stats.

7. ## Re: Align and Compare stats help please

Looks exactly like what I posted. I'm confused.

Did you just want blanks instead of zeroes?

Attachment 261414

8. ## Re: Align and Compare stats help please

@neo999
Sorry but I can not se any correlation
@daffodil11
Why your tab include the 3rd (bottom ) table?
I can not see that on oryginal request.

9. ## Re: Align and Compare stats help please

The tab at the bottom in my example is his second table. I just plopped it somewhere else and used VLOOKUP to automatically align them.

10. ## Re: Align and Compare stats help please

Hi Daffodil11,

I apoligize. I was confused with the second table. YES, this is exactly what I need. However, Can you help me out with step by step instructions on how to create this report? I am very new to excel and clueless. Somone also had mentioned using a "pivot table" which is foreign to me as well.

BTW: This is a small list and the actual list has thousands of rows of data to align. Also, blanks instead of the zeros would visually help me more for site names that have no data. If not possible, that's fine.

I really appreciate it.

11. ## Re: Align and Compare stats help please

Sure. Let's take a trip to Imagination Land.

Imagine a spreadsheet with a, b, c, etc from A1 to A10.

At A50:D200 you have a rows that begin with A, B, C, and data in other columns. You want to match that data to the letters in A1:A10.

VLOOKUP searches the left-most column of a range for matches, and returns whatever column you want.

VLOOKUP(look for what, search where, return what column's data, [0 for exact matches])

VLOOKUP(A1,A50:D200,2,0) will look for whatever is in A1, in A50:D200 looking only at the left-most column of that range, and for the first match it finds it will return the value in the 2nd column, and will return an exact match.

Here's a real example, because pretending can be hard.

Attachment 261674

http://www.techonthenet.com/excel/formulas/vlookup.php

12. ## Re: Align and Compare stats help please

I checked through the information and will also watch a few tutorials. However, at the moment, I'm still confused with how to implement this. I was hoping you can help with step by step of how you prepared the first sample such as:

Step 1: open your report in excel
Step 2: go to the "formula" tab
Step 3: etc...

13. ## Re: Align and Compare stats help please

I don't think I've every clicked on the formula tab, except to define names.

Try this guide:

Attachment 262035

14. ## Re: Align and Compare stats help please

Hi Daffodil11. It Worked! I was finaly able to follow your instructions from your last sample and able to get it working. All the way through step 5. THANK YOU for your time and help.

P.S. I also copied the "percentage" formula from the previous sample. However, I'm getting these two errors (#DIV/0! and #VALUE!) for the match that do not have revenue numbers.

It's also showing "-100%" for some that it finds a number on one of the revenue columns but a "0" on the other. Is there an additional formula to enter in the "percentage" column to make it look "pretty"?

Thank you again.

15. ## Re: Align and Compare stats help please

Like in my last example for step 5, apply IFERROR to avoid error outputs.

If A1 = 1, B1 = 0, then if C1 = A1/B1 the outcome is #DIV/0!

We can avoid this with C1 = IFERROR(A1/B1,""), and the output will be a blank cell.

In any cell that creates an error, simply change the formula to IFERROR(original formula,"")

The VLOOKUP would look like IFERROR(VLOOKUP(BLAH,BLAHBLAH,2,0),"")

And the change would look like IFERROR((STAT2-STAT1)/STAT1,"")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1