Combining and looking up unique fields

Hi all,

I?m stuck on to do this or if this is even possible. I have a dealer number, and under most dealers numbers there are reinsurance companies and types. A dealer can have multiple reinsurance companies
And types as well.

What I am trying to do is combine all the various combination of the reinsurance company name and type buy dealer number which would just be combining these fields together but where I?m stuck is i need to be able to pull both of the combinations into other files when doing a lookup. Of course when doing a vlookup it will only pull the first of the queries it finds but we have multiple rows with the same dealer number and I am needing to pull in all various combinations with the reinsurance pieces.

For example line 50 has dealer number 42095 which goes down to line 59. When I look up this dealer number I am wanting to pull in all the various combinations in Row B and C pertaining to this dealer. Not sure if I am making sense here. I have attached a workbook for reference.

Hi, Thanks for this!

I was wondering where you are inserting the formula in terms of line. I am getting N/A when using it. Also i noticed there is no query that pics up Column C in it. Was that intentional? Thanks again!

Hi, thanks for the help. So what I am trying to achieve is combining what you have in your first pic into one line. So for the number 42095 I want in a new column it to contain all the reinsurance company names and types in one line. So something that would look like 42095Fred GS RE NCFC NCFC-GS Re42095Fred GS RE NCFC2NCFC-GS Re 2....... and then continuing adding the rest of the line items together into one line.

That way when i do a vlookup based on the dealer number of 42095 i can pull in all the relevant reinsurance information into one line. Not sure if that makes sense. Thanks!

to whom are you talking ?

Sorry, this was meant for you

Hi, thanks for the help. So what I am trying to achieve is combining what you have in your first pic into one line. So for the number 42095 I want in a new column it to contain all the reinsurance company names and types in one line. So something that would look like 42095Fred GS RE NCFC NCFC-GS Re42095Fred GS RE NCFC2NCFC-GS Re 2....... and then continuing adding the rest of the line items together into one line.

That way when i do a vlookup based on the dealer number of 42095 i can pull in all the relevant reinsurance information into one line. Not sure if that makes sense. Thanks!

YES! like this. so i would need an add in for this?

If you are on XL2013 you'll need Power Query add-in

latest version of solution is attached

thanks I am on XL Professional Plus 2016. How would i go about doing this if i do not need the add on? thanks again!

This is amazing, i didnt know excel had this feature! Thanks

one last question is there a way to display this all at once instead of searching individually?

????????

all you have in source table

but if you want you can copy Dealer number column and paste somewhere, then Remove Duplicates and next copy/paste into search table and Refresh green table

Search		List
03050		03050, Pauley GS Re NCFC, NCFC-GS Re, 03050, Pauley GS Re NCFC 2, NCFC-GS Re 2
17014		17014, Price GS Re NCFC, NCFC-GS Re, 17014, Price GS Re2, NCFC-GS Re 2
17023		17023, Yokem GS Re NCFC, NCFC-GS Re, 17023, Yokem GS Re NCFC 2, NCFC-GS Re 2
17027		17027, Van GS Re NCFC, NCFC-GS Re, 17027, Van GS Re NCFC 2, NCFC-GS Re 2
17041		17041, Musson-Patout GS Re NCFC, NCFC-GS Re
17050		17050, Gullo Group GS Re NCFC, NCFC-GS Re, 17050, Gullo Group GS Re2, NCFC-GS Re 2
17053		17053, Ronnie Ward GS Re NCFC, NCFC-GS Re, 17053, Ronnie Ward GS Re NCFC 2, NCFC-GS Re 2
17057		17057, John Harvey GS Re NCFC, NCFC-GS Re, 17057, John Harvey GS Re NCFC 2, NCFC-GS Re 2
17060		17060, Lake Charles GS Re NCFC, NCFC-GS Re
17066		17066, Gullo Group GS Re NCFC, NCFC-GS Re, 17066, Gullo Group GS Re2, NCFC-GS Re 2
17071		17071, Allen Krake (NCFC), NCFC-Tricor
17339		17339, Price GS Re NCFC, NCFC-GS Re, 17339, Price GS Re2, NCFC-GS Re 2
17373		17373, Price GS Re2, NCFC-GS Re 2
23035		23035, of Hattiesburg GS Re NCFC, NCFC-GS Re, 23035, of Hattiesburg GS Re NCFC 2, NCFC-GS Re 2
23052		23052, Allen GS Re NCFC, NCFC-GS Re, 23052, Allen GS Re NCFC 2, NCFC-GS Re 2
23054		23054, Oxford GS Re NCFC, NCFC-GS Re, 23054, Oxford GS Re2, NCFC-GS Re 2
23062		23062, Kim's GS Re NCFC, NCFC-GS Re
23071		23071, Oakes GS Re NCFC, NCFC-GS Re, 23071, Oakes GS Re NCFC 2, NCFC-GS Re 2
23077		23077, Oakes GS Re NCFC, NCFC-GS Re, 23077, Oakes GS Re NCFC 2, NCFC-GS Re 2
23319		23319, Allen GS Re NCFC, NCFC-GS Re
35049		35049, Jim - GS Re NCFC, NCFC-GS Re, 35049, Jim - GS Re NCFC 2, NCFC-GS Re 2
35054		35054, Hudiburg GS Re NCFC, NCFC-GS Re
35070		35070, Jim - GS Re NCFC, NCFC-GS Re
35072		35072, Jim - GS Re NCFC, NCFC-GS Re, 35072, Jim - GS Re NCFC 2, NCFC-GS Re 2
42026		42026, Robbins Group GS Re NCFC, NCFC-GS Re
42027		42027, Frank Smith GS Re NCFC, NCFC-GS Re, 42027, Roland F Smith (NCFC), NCFC-Tricor
42057		42057, of Irving GS Re NCFC, NCFC-GS Re, 42057, of Irving GS Re NCFC 2, NCFC-GS Re 2
42095		42095, Fred GS Re NCFC, NCFC-GS Re, 42095, Fred GS Re NCFC 2, NCFC-GS Re 2, 42095, Mark (NCFC), NCFC-Tricor, 42095, Mark (NCFC06), NCFC-Northbay 2006, 42095, Mark (NCFC07 Pre Merger), NCFC-Northbay 2007 Pre Merger, 42095, Mark (NCFC07), NCFC-Northbay 2007, 42095, Steve (NCFC), NCFC-Tricor, 42095, Steve (NCFC06), NCFC-Northbay 2006, 42095, Steve (NCFC07 Pre Merger), NCFC-Northbay 2007 Pre Merger, 42095, Steve (NCFC07), NCFC-Northbay 2007
42138		42138, Mike Calvert GS Re NCFC, NCFC-GS Re, 42138, Mike Calvert GS Re NCFC 2, NCFC-GS Re 2, 42138, Mike Calvert _S. Vaugn 20 GS RE NCFC 2, NCFC-GS Re 2

so...

if the problem is solved, hit Add Reputation (bottom left corner next to the post that was helpful)
and then mark the thread as SOLVED (top above your first post - Thread Tools)

yes, thank you, greatly appreciate your help! will do

Hi Sandy,

You had helped in the past with this file where I asked to combine unique fields. I was revamping the file and needed your help. Im sure its a quick fix for you but I tried and could not get it to work, but what I am trying to have happen is in sheet 2 have the result spit out but not have the result in column A show up. For example the line 1 result in sheet 2 should just show "DA0723 Reinsurance Company, Ltd.,DHW No. 4 Reinsurance Company, Ltd.,DHW Reinsurance Company, Ltd.,DHW Squared Reinsurance Company, Ltd.
"


Is this something you can help with ?

Thanks in advance!

I can't help if I don't understand, there is a big mess in your M also I don't know what are trying to achieve
detailed description should help

Sorry, cleaned up. So the way the file is built right now is i believe it takes the dealer number and combines that with all the different reinsurance names with the same dealer number. I believe what's happening is the combination right now is adding in dealer number every time along with the reinsurance company. What i am trying to have happen is it just adds the reinsurance name without the dealer number.

If you goto sheet2 right now and refresh the data in column C you will see it adding the dealer number every time it finds a reinsurance name tied to that, which makes the text very long. I just need it to add the reinsuance names together not the dealer number.... Not sure if that makes sense?

is that what you want?

Yes this format is perfect! although i do not believe the data is pulling in right. For example Line 46 in Sheet 2 is pulling Moses Auto Group (TAP03376679DAR), but if you goto sheet one where the data is housed that contract (TAP03376679DAR) belongs to the below.

DA0723 Reinsurance Company, Ltd.
DHW No. 4 Reinsurance Company, Ltd.
DHW Reinsurance Company, Ltd.
DHW Squared Reinsurance Company, Ltd.


Is something off with formula?

thanks again

there is no data in row 46 in sheet2

Really? will reattachCapture.JPG

See it now? Thanks

this is a wrong way to compare result query with table from outside of query
there is no connection
sometimes it matches row to row but that's just a coincidence

check this one
see the table SEARCH is sorted Z-A but the result is sorted in another way
if you want connect Excel Table to the Result table you need to add index to the both then sort first and the second by index

don't use empty rows in a table !!!
also blank columns it doesn't make sense !!!

so how would i get the proper results I am looking for? to combine the reinsurance company name in 1 line items based of the dealer number. So duplicate dealer numbers would put in all the reinsurance names into one line.

see this one
but I think you will need to learn Power Query more
Power Query documentation
this is NOT Click&Go software

Thanks , this file is not combining the names into one line like the previous files were. Is that on purpose? That was close to my end result i needed.

Sorry im posting before your next reply! This file seems GOOD. THANK YOU SO MUCH. You are right i need to learn power query ASAP. I will look into this . You have been super helpful and patient. Thanks!

see post#30
is that what you want?

Yes, seems to be it. THANK YOU so much again and thanks for your patience!