I am trying to count the number of customers with the same unique ID who shop at other area stores in our system. For example, how many customers shop at both "Madison" and Westerly". This value tells me the number of customers who "crossover" and shop at both stores.
Attached is a sample of the file I am looking to add formulas to.
Thanks in advance for your feedback.
is "Madison" > Westerly" and Westerly" > "Madison" not equal?
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Try
G5Formula:
Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Hi all- This formula uses these names: Store and Customer_ID. To define a Named Range, select the data, click Define Name on the Formula tab, and type in the name. Then paste this ARRAY FORMULA* in G5 and copy down:
*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.
Please Login or Register to view this content.
Row\Col
Last edited by leelnich; 08-26-2017 at 04:15 AM.
Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
Perfect. This is exactly what I was looking for. Thank you!
This too is a great solution. Adding the names makes this very intuitive. Thank you!
Yes. That is correct. The solutions posted were just what I was looking for. Thank you for view this.
Hi all- @Perk1961, the solutions offered in posts #3 and #4 return COMPLETELY different results. Which one (if either) is correct?
Postscript-27 AUG 2017 - The above statement is incorrect. I failed to adjust Ankur's formula to fit the same data range as mine. Apologies to all.
Last edited by leelnich; 08-27-2017 at 01:11 PM.
The one I used was by shukla. That said, after looking at Leelnich's formula the version by Shukla did not address duplicate records (Customer ID's with the same Store). The formula by leelnich produced the same results but excluded duplicates which made for a more robust solution. The only problem I found was that Leelnich included an attached file where that formula was properly applied to the first row (G5:J5) but then was not copied down to the cells below. Once I copied that formula to all the cells then the results were the same as Shukla's.
Again, the formula by Shukla and Leelnich both seem to work the same if there are no duplicates, but the formula by Leelnich's also excludes duplicates.
At the end of the day, my data did not have duplicates so either one worked for me. Also, the formula by Shukla was MUCH faster than the one by Leelnich. Not sure why, but just guessing there were more calculations that had to take place to assure duplicates were not included.
Last edited by Perk1961; 08-27-2017 at 11:09 AM.
Apologies to all. My original assertion in post #8 was incorrect; Ankur's formula does in fact agree with mine (if duplicates are removed). I failed to correctly adjust the ranges used in his formula.
Nicely done, Ankur!
Last edited by leelnich; 08-27-2017 at 01:43 PM.
BTW @shukla.ankur281190, I think the "bins" clause of your formula could be simplified:Any reason you didn't go with that? - LeePlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks