Data Validation with same name and multi addresses

W

whytewolves

New Member
Joined
Sep 16, 2015
Messages
21
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
Book1
CDEFGHIJKL
1CustomerAddress
2Adrian L2714 Crest Ct.Hoerner YMCAKHS: 2285 Middle Rd.
3 1st Name Last NameColumn1HomeWorkOther
4Aaliyah HAaliyah H401 N 3rd St. #9
5Abby BAbby B502 I St.
6Abigail TAbigail T60 S 9th St.
7Adam JAdam J2020 Palean St.
8Adam KAdam K3761 250tth Ave.
9Adrian LAdrian L2714 Crest Ct.Hoerner YMCAKHS: 2285 Middle Rd.
10Adrian LAdrian L513 N 14th St.
11Adrianna WAdrianna WKHS: 2285 Middle Rd.
12
Customer
Cell Formulas
RangeFormula
J2:L2J2=XLOOKUP(G2,Table2[Column1],Table2[[Home]:[Other]],"No Address Located")
E4:E11E4=CONCATENATE([@[ 1st Name]]," ",[@[ Last Name]])
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
G2List=$E4:$E11
H2List=$J2#





So, I have a list of names along with multiple addresses for each customer. Some customers have same first name and same last name intitial. When I select one, no matter which one, it only produces the first rows addresses into the drop down in H2 and lists them in J2:L2. This list is 534 strong and a growing list, and these addresses I need populated with the appropriate named row.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
I would suggest you load your data table to Power Query. Filter on the combined name. Use any name.

Load the cells E1:E2 as a table to Power Query. In PQ, right click on the value in the PQ editor and select Drill Down. Close and Load this table to Connection only. In the original query, change the filter value to the name of the new table. Close and Load the new Table to a new worksheet. Now when ever you change the look up value in E2, click on Data Refresh All to see your updated results.
 
Upvote 0
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this is what you want.

22 01 06.xlsm
CDEFGHIJ
1CustomerAddress
2Adrian L2714 Crest Ct.
3 1st Name Last NameColumn1HomeWorkOtherHoerner YMCA
4Aaliyah HAaliyah H401 N 3rd St. #9KHS: 2285 Middle Rd.
5Abby BAbby B502 I St.513 N 14th St.
6Abigail TAbigail T60 S 9th St.
7Adam JAdam J2020 Palean St.
8Adam KAdam K3761 250tth Ave.
9Adrian LAdrian L2714 Crest Ct.Hoerner YMCAKHS: 2285 Middle Rd.
10Adrian LAdrian L513 N 14th St.
11Adrianna WAdrianna WKHS: 2285 Middle Rd.
12
Address Drop-Down
Cell Formulas
RangeFormula
J2:J5J2=FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,FILTER(Table2[[Home]:[Other]],Table2[Column1]=G2,"No Address Located"))&"</c></p>","//c")
E4:E11E4=CONCATENATE([@[ 1st Name]]," ",[@[ Last Name]])
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
H2List=J2#
 
Upvote 0
Peter_SSs said:
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this is what you want.

22 01 06.xlsm
CDEFGHIJ
1CustomerAddress
2Adrian L2714 Crest Ct.
3 1st Name Last NameColumn1HomeWorkOtherHoerner YMCA
4Aaliyah HAaliyah H401 N 3rd St. #9KHS: 2285 Middle Rd.
5Abby BAbby B502 I St.513 N 14th St.
6Abigail TAbigail T60 S 9th St.
7Adam JAdam J2020 Palean St.
8Adam KAdam K3761 250tth Ave.
9Adrian LAdrian L2714 Crest Ct.Hoerner YMCAKHS: 2285 Middle Rd.
10Adrian LAdrian L513 N 14th St.
11Adrianna WAdrianna WKHS: 2285 Middle Rd.
12
Address Drop-Down
Cell Formulas
RangeFormula
J2:J5J2=FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,FILTER(Table2[[Home]:[Other]],Table2[Column1]=G2,"No Address Located"))&"</c></p>","//c")
E4:E11E4=CONCATENATE([@[ 1st Name]]," ",[@[ Last Name]])
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
H2List=J2#
Click to expand...
So this works great; except I have know idea what the "<p><c>"&TEXTJOIN("</c><c>" and &"</c></p>","//c" are for. I'm not sure what they reference, if that explains my question here.
So this mini sheet I provided is not the entire or even actual sheet this formula needs to go on.
The customer dropdown and address dropdown are on a entirely different sheet.
 
Upvote 0
whytewolves said:
I have know idea what the "<p><c>"&TEXTJOIN("</c><c>" and &"</c></p>","//c" are for. I'm not sure what they reference
Click to expand...
They do not reference anything on your sheet. They help produce a text string the format of which allows the FILTERXML function to work. Explaining that here is not feasible I'm afraid.

whytewolves said:
The customer dropdown and address dropdown are on a entirely different sheet.
Click to expand...
That should not be a problem. Here is an example that you might be able to adapt.

whytewolves.xlsm
CDEFGH
1
2
3 1st Name Last NameColumn1HomeWorkOther
4Aaliyah HAaliyah H401 N 3rd St. #9
5Abby BAbby B502 I St.
6Abigail TAbigail T60 S 9th St.
7Adam JAdam J2020 Palean St.
8Adam KAdam K3761 250tth Ave.
9Adrian LAdrian L2714 Crest Ct.Hoerner YMCAKHS: 2285 Middle Rd.
10Adrian LAdrian L513 N 14th St.
11Adrianna WAdrianna WKHS: 2285 Middle Rd.
12
13
Sheet1
Cell Formulas
RangeFormula
E4:E11E4=CONCATENATE([@[ 1st Name]]," ",[@[ Last Name]])


whytewolves.xlsm
CDEFG
1CustomerAddressCustomersAddresses
2Adrian LAaliyah H2714 Crest Ct.
3Abby BHoerner YMCA
4Abigail TKHS: 2285 Middle Rd.
5Adam J513 N 14th St.
6Adam K
7Adrian L
8Adrianna W
9
Sheet2
Cell Formulas
RangeFormula
F2:F8F2=SORT(UNIQUE(Table2[Column1]))
G2:G5G2=FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,FILTER(Table2[[Home]:[Other]],Table2[Column1]=Sheet2!C2,"No Address Located"))&"</c></p>","//c")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
C2List=F2#
D2List=$G$2#


1641794291573.png
 
Upvote 0
Solution
Peter_SSs said:
They do not reference anything on your sheet. They help produce a text string the format of which allows the FILTERXML function to work. Explaining that here is not feasible I'm afraid.


That should not be a problem. Here is an example that you might be able to adapt.

whytewolves.xlsm
CDEFGH
1
2
3 1st Name Last NameColumn1HomeWorkOther
4Aaliyah HAaliyah H401 N 3rd St. #9
5Abby BAbby B502 I St.
6Abigail TAbigail T60 S 9th St.
7Adam JAdam J2020 Palean St.
8Adam KAdam K3761 250tth Ave.
9Adrian LAdrian L2714 Crest Ct.Hoerner YMCAKHS: 2285 Middle Rd.
10Adrian LAdrian L513 N 14th St.
11Adrianna WAdrianna WKHS: 2285 Middle Rd.
12
13
Sheet1
Cell Formulas
RangeFormula
E4:E11E4=CONCATENATE([@[ 1st Name]]," ",[@[ Last Name]])


whytewolves.xlsm
CDEFG
1CustomerAddressCustomersAddresses
2Adrian LAaliyah H2714 Crest Ct.
3Abby BHoerner YMCA
4Abigail TKHS: 2285 Middle Rd.
5Adam J513 N 14th St.
6Adam K
7Adrian L
8Adrianna W
9
Sheet2
Cell Formulas
RangeFormula
F2:F8F2=SORT(UNIQUE(Table2[Column1]))
G2:G5G2=FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,FILTER(Table2[[Home]:[Other]],Table2[Column1]=Sheet2!C2,"No Address Located"))&"</c></p>","//c")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
C2List=F2#
D2List=$G$2#


View attachment 54849
Click to expand...
Thank you so much. I just needed to figure out the cells you were referencing and it all came together. Thank you emensly for your help.
 
Upvote 0
Peter_SSs said:
You're welcome. Glad to help. :)
Click to expand...
I am having one issue still. I use this on an iPad, for work. I know that Apple and Microsoft have their issues, hence why I am getting away from the VBA I was previously using.
I added this to the formula: =IFERROR(TRANSPOSE(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,FILTER(Table2[[Home]:[Other]],Table2[Column1]=TableDeliveryLog[@Customer],"Address Not Found"))&"</c></p>","//c")),"")
Works great on PC. Then the iPad; it added this: =IFERROR(TRANSPOSE(.xmlFILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,FILTER(Table2[[Home]:[Other]],Table2[Column1]=TableDeliveryLog[@Customer],"Address Not Found"))&"</c></p>","//c")),"")
I believe that is what it added. I removed it, changed it, and now it places no information in the cell; but the information is in the function bar
 
Upvote 0
I do not have a Mac to test anything but from the FILTERXML function link that I provided in post #5 comes:
The FILTERXML function is not available in Excel for the web and Excel for Mac
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

Share this page

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