Compare 2 lists / conduct gap analysis (XL 2007)

[AMR]

I'm looking for an easier way to conduct a gap analysis between two lists in
Excel 2007.

My company has a tool that sends out documents to clients based on
distribution lists. There are two problems that continually occur:

1) Some of the clients that should receive the documents are not included in
the distribution lists

2) Due to how the distribution lists are set up and cross-reference each
other, more clients receive the documents than intended

I am trying to compare the list of clients to the list of recipients. I
would like a better way to determine where the gaps are in the recipients
list.

My current process is to manually cut/paste cells to line up the content so
that the end result looks something like this:

Clients Recipients
------- ----------
AAA AAA
BBB
CCC
DDD DDD
EEE
FFF
GGG GGG
HHH
III III
JJJ JJJ


Is there a better process using functions or pivot tables that would give me
the same or similar results? The most important part is to identify the
clients who are not receiving the documents (i.e., find the gaps in the
Recipients list).

 

[Dave Peterson]

I like to get a consolidated list (both clients and recipients in your example),
then use two adjacent columns to indicate if they're on the respective lists.

To get the consolidated list, I'd:

Start a new worksheet
Copy the first list to this worksheet starting in A1 (include a single header).
Copy the second list (without the headers) directly under this first list.

Use data|filter|autofilter to filter the unique records to a new column (column
B).

Debra Dalgleish describes it here:
http://contextures.com/xladvfilter01.html#FilterUR

Delete column A

Sort column A (if you like) -- column B shifted to column A after deleting that
old column A.

then use this in B2:

=isnumber(match(a2,sheet1!a:a,0))
to see if the name in A2 matches any of the first list (on sheet1 column A)

And
=isnumber(match(a2,sheet2!a:a,0))
to see if A2 matches the second list on sheet2.

Then drag these formulas down as far as required (to match column A).

Then I apply data|filter|autofilter to those 3 columns.

I can filter to see:
True, true (on both lists)
False, true (on the second, not the first)
true, false (on the first, not the second)

and if you see
false, false
then something very bad happened.

 

[AMR]

Dave, thanks a lot! It worked!

Just one question on the ISNUMBER matching formula.

Where you had this:

=isnumber(match(a2,sheet1!a:a,0))
to see if the name in A2 matches any of the first list (on sheet1 column A)

And
=isnumber(match(a2,sheet2!a:a,0))
to see if A2 matches the second list on sheet2.

Shouldn't that second formula refer to Sheet 1, column B? Like this?
=ISNUMBER(MATCH(A2,Sheet1!B:B,0))

Maybe I misunderstood your directions. Does my setup match what you meant?

I set up my source sheet (Sheet1) with 2 columns: A = Clients, B = Recipients

I set up the consolidated/matching sheet (Sheet 2) with 3 columns:
A = consolidated list of client ID codes
B = formula #1 =ISNUMBER(MATCH(A2,Sheet1!A:A,0))
C = formula #2 =ISNUMBER(MATCH(A2,Sheet1!B:B,0))

--------
Just had one other hiccup where I learned that differences in cell format
between the source lists and the consolidated list can affect if the match
returns TRUE or FALSE correctly.

I had applied "text" format to my original Client and Recipeint lists (sheet
1) because the client ID codes are a mix of letters and numbers. But the
consolidated list (sheet 2) was formatted as "general," so some of the values
didn't return right. Everything matched correctly once I got all the formats
set to "general."

 

[Dave Peterson]

I figured your data was in separate worksheets and the stuff you showed was
after you started manipulating it.

But yep, you want to point at whatever column contains the data--no matter what
sheet/column it's in.

=======
Be careful with the format stuff. Just changing the format of a cell isn't
enough to change the underlying value.

If you have a cell formatted as text and type 123 in it, then changing the
format to General doesn't change the value from a string to a number.

You have to do something else.

You could reformat the cell and then retype the entry.
You could use some sort of data|text to columns

I like this technique to convert text numbers to number numbers.
Copy an empty cell
select the range of offending cells
edit|paste special|check add

And your text numbers will become number numbers.

 

[AMR]

I had already done my manual gap analysis before I posted, so I just used the
data from that to test your instructions. This distribution thing happens
regularly so it will be a time-saving boon in the future.

I discovered what you meant about the formatting. The Help instructions are
very similar to your method---they said to type a 1 in a cell and use paste
special/multiply---and it achieved the desired result in the end. To-may-to,
to-mah-to.

Thanks again for all your help.

 

[Dave Peterson]

One of the differences in those techniques is what happens with empty cells --
it'll be different after adding the empty cell than multiplying by the cell with
1 (and you don't have to clean up that cell with 1, either!)