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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Domenic

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

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,201

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,368
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,883
Messages
5,543,017
Members
410,583
Latest member
gazz57
Top