+ Reply to Thread
Results 1 to 15 of 15

Align and Compare stats help please

  1. #1
    Registered User
    Join Date
    08-24-2013
    Location
    Los Angeles, Ca
    MS-Off Ver
    Excel 2010
    Posts
    7

    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.
    Attached Files Attached Files
    Last edited by neo999; 08-26-2013 at 07:50 PM.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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
    Last edited by daffodil11; 08-26-2013 at 04:53 PM. Reason: Logic

  3. #3
    Registered User
    Join Date
    08-24-2013
    Location
    Los Angeles, Ca
    MS-Off Ver
    Excel 2010
    Posts
    7

    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.

  4. #4
    Registered User
    Join Date
    08-24-2013
    Location
    Los Angeles, Ca
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Align and Compare stats help please

    Help Anyone?

  5. #5
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Align and Compare stats help please

    How would your outcome look like?
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  6. #6
    Registered User
    Join Date
    08-24-2013
    Location
    Los Angeles, Ca
    MS-Off Ver
    Excel 2010
    Posts
    7

    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.
    Attached Files Attached Files

  7. #7
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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. #8
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    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. #9
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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. #10
    Registered User
    Join Date
    08-24-2013
    Location
    Los Angeles, Ca
    MS-Off Ver
    Excel 2010
    Posts
    7

    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. #11
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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

    Here's additional information on VLOOKUP:
    http://www.techonthenet.com/excel/formulas/vlookup.php

  12. #12
    Registered User
    Join Date
    08-24-2013
    Location
    Los Angeles, Ca
    MS-Off Ver
    Excel 2010
    Posts
    7

    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...

    Thanks in advance.

  13. #13
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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. #14
    Registered User
    Join Date
    08-24-2013
    Location
    Los Angeles, Ca
    MS-Off Ver
    Excel 2010
    Posts
    7

    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.
    Last edited by neo999; 08-30-2013 at 08:28 PM.

  15. #15
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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,"")

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need to compare 2 columns and align them next to the match
    By naenightlucky213 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-09-2013, 06:14 AM
  2. complex compare and align data help.
    By scriblesvurt in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-20-2007, 10:24 AM
  3. compare and align data
    By scriblesvurt in forum Excel General
    Replies: 1
    Last Post: 03-19-2007, 01:27 PM
  4. Compare and align columns of data
    By JGouger in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-11-2005, 12:30 PM
  5. Macro to align & compare multiple columns with several rows
    By Manav Ram via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-07-2005, 04:10 PM

Bookmarks

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