Excel---How to Group and Compare????

[fvlmasl2]

Hello,

I have 2 spreadsheets, with identical columns, one for this year and one for last year (customer data) in these 2 ss i have many of the same customer numbers. I need to group these so instead of having 100 customer number 12345, i would only have 1. Then i would like to compare the two and see which customers are in one and not the other. I have been playing around with this for a little time now and can't seem to get it to work just the way i need it too.......


Thanks
fvlmasl2

 

[ggnl]

First, use a pivot table to group the customers. Put the customer in the left column of the table and any data element in the data section and it should group the customers for you.

Second, you can use the MATCH function to compare the two lists. Use "0" as the match type. You'll have to do it twice, comparing List A to List B, then List B to List A. Any values that dont match will come out as NA, which you can then filter out.

Sorry about being so brief with my answers, but it's almost time to go home. Just ask if you need more help.

 

[fvlmasl2]

Well, i have 1 spreadsheet 2 columns how do i compare these 2 cloumns....


Thanks
fvlmasl2

 

[ggnl]

Assuming you have the columns in A and B with a heading row your formula would look something like =MATCH(B2,$A$2:$A$X,0). Any customers in column B that aren't in column A will return the value #N/A, which you can then filter out.

Then in another column you could do the formula =MATCH(A2,$B$2:$B$X,0) to find values in column A that arent in column B.

 

[fvlmasl2]

Got it....thank you very much. i am still a little fuzzy about the pivot table. i don't seem to be able to get it to do what it is suppose to do....


fvlmasl2

 

[ggnl]

I'll attempt a quick pivot table tutorial (with screenshots even).

1. You start off with two columns. One is the column with the customer numbers you want to group. The other is some kind of data column (it doesn't matter what that data is, you just need two columns).

2. Highlight the two columns and select select pivot table. That should get you to this point

3. Drag the "Customer" button to the Row Field Section. Then Drag the "Data" button to the Data Item Section. That should get you here.

As you can see, you now have a list of the customers in the pivot table. You can copy and paste the column from the pivot table and do whatever you want with it.

Hope this helps.

 

[fvlmasl2]

Thanks...I will give it a try.......



fvlmasl2