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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,724
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!
 

T2D2

New Member
Joined
May 22, 2006
Messages
23
This is returning a #VALUE! error in Column A. Any help as to why?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,724

ADVERTISEMENT

Did you first download and install the Morefunc.xll add-in?
 

T2D2

New Member
Joined
May 22, 2006
Messages
23
Yes. I can build the function using the builder window and Morefunc is checked in my Add-Ins menu...
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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.
 

T2D2

New Member
Joined
May 22, 2006
Messages
23
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"
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,724
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...
 

T2D2

New Member
Joined
May 22, 2006
Messages
23
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.
 

Forum statistics

Threads
1,141,427
Messages
5,706,377
Members
421,446
Latest member
rena jhon

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
Top