If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Compare 2 lists / conduct gap analysis (XL 2007)
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). |
#2
|
|||
|
|||
Compare 2 lists / conduct gap analysis (XL 2007)
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 he 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 wrote: 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 |
#3
|
|||
|
|||
Compare 2 lists / conduct gap analysis (XL 2007)
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" wrote: 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 he 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 wrote: 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 . |
#4
|
|||
|
|||
Compare 2 lists / conduct gap analysis (XL 2007)
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 wrote: 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" wrote: 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 he 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 wrote: 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 . -- Dave Peterson |
#5
|
|||
|
|||
Compare 2 lists / conduct gap analysis (XL 2007)
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" wrote: 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 wrote: 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" wrote: 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 he 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 wrote: 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 . -- Dave Peterson . |
#6
|
|||
|
|||
Compare 2 lists / conduct gap analysis (XL 2007)
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!) AMR wrote: 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" wrote: 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 wrote: 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" wrote: 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 he 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 wrote: 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 . -- Dave Peterson . -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|