+ Reply to Thread
Results 1 to 14 of 14

Comparing Data From Multiple Reports and Multiple Columns

  1. #1
    Registered User
    Join Date
    07-02-2014
    Location
    Thennessee
    MS-Off Ver
    2012 Pro
    Posts
    21

    Comparing Data From Multiple Reports and Multiple Columns

    Hey Guys -

    I am working in an environment where a tool is reporting random statuses for various systems so I am trying to identify different details between each day. Over the past 3 days, I've collected about a dozen columns of data for ~30,000 systems for a site. Each is saved in it's own file. Below are a few rows with headers for example:
    firstam1.jpg

    What I'm trying to do now is create a report which shows the following:
    - List rows which were not listed on any of the 3 daily reports (Comparing only columns A, B & C)
    - Show which days the compared rows did / did not appear
    - Highlight or separately list systems (column A) which appeared in multiple reports, but the B and/or C columns were different
    - The final report should show the full row (all original columns) for systems listed - even though only comparing hostnames.

    If only comparing 2 days, I could just add a new column showing the date the report was taken to each, combine them into a single spreadsheet, then remove duplicates. Since i have 3 days, I'm unsure of the best method, though.

    Any suggestions? If it helps, I have Excel 2016 plus have KuTools installed. Hope I explained this well enough.

    Thank You!
    Last edited by bzowk; 11-09-2018 at 05:50 PM. Reason: Title edit

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Need Assistance With Difficult Comparison

    I'm totally confused by this statement:

    - List rows which were not listed on any of the 3 daily reports (Comparing only columns A, B & C)

    If they appear in A, B, and C - are they not listed? What does that mean in actuality - are you looking for specific dates based on other columns.... an example of what that means would be useful.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    07-02-2014
    Location
    Thennessee
    MS-Off Ver
    2012 Pro
    Posts
    21

    Re: Need Assistance With Difficult Comparison

    Sorry for the confusion - Let me try this again...

    Overall, SCCM is providing random results for which systems it discovers day to day as well as if they have clients or not. This doesn't have to be a single report, also. I'm trying to find out
    - A list of systems (Column A) which are not listed on all 3 daily captures
    - If the "Client Installed" state (Column B) differs between days regardless of how many days it was listed / not listed on
    - If the "Last Install Error Code" (Column C) differs between days regardless of how many days it was listed / not listed on
    The resulting report(s) should list all common columns for the system and a duplicate row for systems where B/C differed

    If it helps, I just combined the 3 spreadsheets to create a "master" one. Prior to doing so, I added a new column (D) which shows the date the data was captured.
    ss2.jpg

    Hopefully, that helps. Any suggestions would be appreciated - Thanks!

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Need Assistance With Difficult Comparison

    So let's start with:

    "- A list of systems (Column A) which are not listed on all 3 daily captures"

    What would indicate the daily capture? A date in column D? A date and time in column D? Can a system be captured more than once in a day? Would a column of formulas like

    =IF(COUNTIF(A:A,A2)<>3,"Not listed 3 times","OK")

    find systems that are not listed on all 3 daily captures?

  5. #5
    Registered User
    Join Date
    07-02-2014
    Location
    Thennessee
    MS-Off Ver
    2012 Pro
    Posts
    21

    Re: Need Assistance With Difficult Comparison

    Bernie Deitrick,

    Thanks for the reply!

    I think I see what you are saying. So basically that formula says if the text in A (of the row it was used) is not listed in the A column 3 times, place "OK" in the formula's cell - else - place "not listed 3 times" - correct? I just tried that and it seems to have worked!. Now I'll just filter that column and copy the rows needed to a new spreadsheet. Thank You!!

    As for the only thing remaining, the contents of cells B & C should not differ for each of the 1-3 potential rows per system. I need to identify ones that do.

    Thanks again!!
    Last edited by jeffreybrown; 11-09-2018 at 04:47 PM. Reason: Removed full quote!

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Need Assistance With Difficult Comparison

    For the requirement that B and C are the same for any specific value in A, try a column of formulas like

    =IF(COUNTIF(A:A,A2)=COUNTIFS(A:A,A2,B:B,B2,C:C,C2),"OK","Something changed here....")

  7. #7
    Registered User
    Join Date
    07-02-2014
    Location
    Thennessee
    MS-Off Ver
    2012 Pro
    Posts
    21

    Re: Need Assistance With Difficult Comparison

    Quote Originally Posted by Bernie Deitrick View Post
    For the requirement that B and C are the same for any specific value in A, try a column of formulas like

    =IF(COUNTIF(A:A,A2)=COUNTIFS(A:A,A2,B:B,B2,C:C,C2),"OK","Something changed here....")
    Thanks - I just tried it, but it doesn't seem to work. Below is a screenshot...
    ss3.jpg

    As you can see, there are 3 rows per hostname for each of the 3 dates. Column P has the suggested formula, but is resulting in "Something changed here" despite the data in each system's B cells being the same and/or all data in each of their "C" cells being the same. To note, I used the exact formula you suggested in P2 which I then drug down a few (processing 90,000+ rows takes a few minutes)

    If it helps, below's a screenshot of what is displayed when the suggested formula is selected:
    ss4.jpg

    What do you think? Thanks again

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Need Assistance With Difficult Comparison

    Let's try some formula logic troubleshooting:

    Use this in Q2:

    =COUNTIF(A:A,A2)

    and this in R2:

    =COUNTIFS(A:A,A2,B:B,B2,C:C,C2)

    Copy down a few rows and see what numbers you are getting....

  9. #9
    Registered User
    Join Date
    07-02-2014
    Location
    Thennessee
    MS-Off Ver
    2012 Pro
    Posts
    21

    Re: Comparing Data From Multiple Reports and Multiple Columns

    ... Post Title Changed...

    OK, I just put those two in which resulted in "3" for Q2 and "0" for R2. Dragging them down yielded the same results as should below...
    ss5.jpg
    ss6.jpg

    Thanks

  10. #10
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Comparing Data From Multiple Reports and Multiple Columns

    Change R2 to

    =COUNTIFS(A:A,A2,B:B,B2,C:C,IF(C2="","",C2))

    and if it returns 3 try

    =IF(COUNTIF(A:A,A2)=COUNTIFS(A:A,A2,B:B,B2,C:C,IF(C2="","",C2)),"OK","Something changed here....")

  11. #11
    Registered User
    Join Date
    07-02-2014
    Location
    Thennessee
    MS-Off Ver
    2012 Pro
    Posts
    21

    Re: Comparing Data From Multiple Reports and Multiple Columns

    I believe that did it - I can't thank you enough!

    Out of curiosity, would that formula work for systems which only had 2 rows instead of 3? Not that big of a deal, but curious. I plan to test it, but the CPU is wrapped up in processing the changes right now.

    Thank again!

  12. #12
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Comparing Data From Multiple Reports and Multiple Columns

    Because you are comparing counts, it will work for any number of rows - you may need to use both sets of formulas to identify those that do not have 3 and those that changes, or you could change to one formula:

    =IF(AND(COUNTIF(A:A,A2)=3,COUNTIFS(A:A,A2,B:B,B2,C:C,IF(C2="","",C2))=3),"OK","Something changed or is missing here....")

    or to be more specific in the return:

    =IF(COUNTIF(A:A,A2)<>3,"Doesn't have 3",IF(COUNTIFS(A:A,A2,B:B,B2,C:C,IF(C2="","",C2))=3,"OK","Something changed here...."))
    Last edited by Bernie Deitrick; 11-09-2018 at 06:28 PM.

  13. #13
    Registered User
    Join Date
    07-02-2014
    Location
    Thennessee
    MS-Off Ver
    2012 Pro
    Posts
    21

    Re: Comparing Data From Multiple Reports and Multiple Columns

    Perfect - You saved me today - I really appreciate your help with this. I hope you have a good weekend!

  14. #14
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Comparing Data From Multiple Reports and Multiple Columns

    I'm happy to have helped and happy to report that I will have a good weekend - with Monday being a holiday, I have three days to relax!

+ 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. Replies: 8
    Last Post: 10-14-2015, 11:58 AM
  2. it's difficult
    By ky4068 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-05-2014, 10:08 AM
  3. Replies: 2
    Last Post: 12-20-2013, 12:12 AM
  4. VBA Script Assistance for Table Comparison by Row
    By Iggy2k13 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-27-2013, 06:35 PM
  5. Assistance: Conditional formatting comparison between two FILES.
    By omni13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-29-2013, 06:52 AM
  6. Help, too difficult for me.
    By Menno in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-19-2006, 09:53 AM
  7. Too difficult for me, please help.
    By Menno in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2005, 09:01 AM

Tags for this Thread

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