Multiple and repeating returns help

T2D2

New Member
Joined
May 22, 2006
Messages
23
I have the spreadsheet below. I am running multiple VLOOKUP to return customer numbers. Some are returning multiple numbers (highlighted in green) and I would like to be able to write a formula in Column A that if all returned numbers in Row 2 were alike it would return 653180 in A2. And in Row 7, since there are differences, it would return
547309 , 547317. All help appreciated.
Book1
ABCDEFGH
1CustomerIDCustomerNameORIGADD1ORIGADD2REVADD1REVADD2NAME1NAME2
2AAAAAAA653180653180653180
3
4
5
6
7BBBBBBBBB547309547317547309547317547309
8
9CCCCCCCCC652140652140
10DDDDDDDDD230772230772
Sheet 1
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
So basically, for each row, you'd like the unique values returned to the corresponding cell in Column A, is this right? If so, download and install the free add-in Morefunc.xll, and try...

A2, copied down:

=SUBSTITUTE(MCONCAT(UNIQUEVALUES(", "&C2:H2,1)),", ","",1)

Hope this helps!
 
Upvote 0
Did you first download and install the Morefunc.xll add-in?
 
Upvote 0
Yes. I can build the function using the builder window and Morefunc is checked in my Add-Ins menu...
 
Upvote 0
Yes. I can build the function using the builder window and Morefunc is checked in my Add-Ins menu...

You need to confirm the formula with control+shift+enter, not just enter then copy down.
 
Upvote 0
You need to confirm the formula with control+shift+enter, not just enter then copy down.

What exactly does this do "confirm the formula with control+shift+enter"
 
Upvote 0
What exactly does this do "confirm the formula with control+shift+enter"

Have a look at the help menu, using 'array formula' as the search term...
 
Upvote 0
Thanks for the help. I think with some slight mods this might work...still not real clear on the array thing, but I'll figure it out.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top