VLOOKUP, CONDITION CHECK THEN CONCATENATE

E

ekhawaja

Board Regular
Joined
Dec 16, 2018
Messages
98
Office Version
  1. 365
Hello Wonderful Community,

I am looking for a formula to put in col 2 of sheet 2 (lookup and concatenate) that will lookup the value of Sheet 2 Col A by going into sheet 1 Col H, once the value is found, it will look for a UNIQUE combo in col J and col U (unique because the value combos are repeated, i don't care about repeats). If a unique combo set is found, formula should output the value from Col U. Now if another unique col J col U combo is found, i want to AMMEND (CONCATENATE) that.

The sample solution will make more sense, I color coded it so it is easier to check.

Sheet 1
col HCol JCol U
43012356 TUE
43012356 TUE
43012356 TUE
43012356 TUE
430123824 TUE
43109333 TUE
43109333 TUE
43106116 TUE
43106156 TUE
43106117 TUE
43106116 TUE
431061106 TUE
43106116 TUE
43012356 TUE
431061212 TUE
431061211 TUE
431061212 TUE
43109333 TUE
43109353 TUE
43109333 TUE
43109333 TUE
43109364 TUE
43109323 TUE
43109333 TUE
43109333 TUE
43109333 TUE
.

Sheet 2LOOKUP AND CONCATENATE
4310933 TUE, 3 TUE, 4 TUE, 3 TUE
4301236 TUE
4310616 TUE, 6 TUE, 7 TUE, 6 TUE,12 TUE, 11 TUE
.

Thank you so much in advance for all the time and help.
 
Sort by date Sort by votes
ekhawaja said:
Hello @AhoyNC

okay so I need to add another criteria (COL I), so we need to look for unique combo among col H, I, J,U and concatenate the results of Col U.

see sample:
Click to expand...

ekhawaja said:
Sheet 1
col HCol ICol JCol U
4301234056 TUE
4301234056 TUE
430123356 TUE
4301234044 TUE
4301234044 TUE
431093133 TUE
431093233 TUE
431061216 TUE
431093233 TUE
431061356 TUE
431061417 TUE
431061316 TUE
4310612106 TUE
4310934036 TUE
431061416 TUE
4301234056 TUE
4310613212 TUE
4310613211 TUE
4310612212 TUE
431093233 TUE
431093153 TUE
431093133 TUE
431093333 TUE
431093264 TUE
431093133 TUE
431093133 TUE
431093233 TUE
431093233 TUE
Click to expand...




Sheet 2LOOKUP AND CONCATENATE
4310933 TUE, 3 TUE, 6 TUE, 3 TUE, 4 TUE
4301236 TUE, 6 TUE, 4 TUE

1742090378425.png
 
Upvote 0
Try:
Note I got a different result for 431093. It counted the blank cells in column I.
Sheet 2
Book3
AB
14310933 TUE. 3 TUE. 6 TUE. 3 TUE. 3 TUE. 4 TUE
24301236 TUE. 6 TUE. 4 TUE
Sheet2
Cell Formulas
RangeFormula
B1:B2B1=TEXTJOIN(". ",,TRANSPOSE(CHOOSECOLS(UNIQUE(FILTER(HSTACK(Sheet1!$H$2:$H$29,Sheet1!$I$2:$I$29,Sheet1!$J$2:$J$29,Sheet1!$U$2:$U$29),Sheet1!$H$2:$H$29=A1)),4)))


Sheet 1
Book3
HIJTU
1col HCol ICol JCol U
24301234056 TUE
34301234056 TUE
4430123356 TUE
54301234044 TUE
64301234044 TUE
7431093133 TUE
8431093233 TUE
943106116 TUE
10431093233 TUE
1143106156 TUE
1243106117 TUE
1343106116 TUE
14431061106 TUE
154310934036 TUE
1643106116 TUE
174301234056 TUE
18431061212 TUE
19431061211 TUE
20431061212 TUE
21431093233 TUE
22431093153 TUE
2343109333 TUE
2443109333 TUE
25431093264 TUE
26431093133 TUE
27431093133 TUE
28431093233 TUE
29431093233 TUE
Sheet1
 
Upvote 0
AhoyNC said:
Try:
Note I got a different result for 431093. It counted the blank cells in column I.
Sheet 2
Book3
AB
14310933 TUE. 3 TUE. 6 TUE. 3 TUE. 3 TUE. 4 TUE
24301236 TUE. 6 TUE. 4 TUE
Sheet2
Cell Formulas
RangeFormula
B1:B2B1=TEXTJOIN(". ",,TRANSPOSE(CHOOSECOLS(UNIQUE(FILTER(HSTACK(Sheet1!$H$2:$H$29,Sheet1!$I$2:$I$29,Sheet1!$J$2:$J$29,Sheet1!$U$2:$U$29),Sheet1!$H$2:$H$29=A1)),4)))


Sheet 1
Book3
HIJTU
1col HCol ICol JCol U
24301234056 TUE
34301234056 TUE
4430123356 TUE
54301234044 TUE
64301234044 TUE
7431093133 TUE
8431093233 TUE
943106116 TUE
10431093233 TUE
1143106156 TUE
1243106117 TUE
1343106116 TUE
14431061106 TUE
154310934036 TUE
1643106116 TUE
174301234056 TUE
18431061212 TUE
19431061211 TUE
20431061212 TUE
21431093233 TUE
22431093153 TUE
2343109333 TUE
2443109333 TUE
25431093264 TUE
26431093133 TUE
27431093133 TUE
28431093233 TUE
29431093233 TUE
Sheet1
Click to expand...
This seems to work so far! thank you so much.
 
Upvote 0
@AhoyNC

I need one more data field. In sheet 2 I need to vlookup 2 conditions (430123 and 40), and in sheet 1 you will see the values are repeated in col H and Col I, I want to concatenate col U if col H and col I matches the input condition and col J has a value.

Pls see below sample for clarity.
sheet 1
col HCol ICol JCol U
4301234056 TUE
4301234456 TUE
430123356 TUE
4301234044 TUE
4301234434 TUE
431093133 TUE
430123633 TUE
431061216 TUE
431093233 TUE
430123643 TUE
431061417 TUE
430123526 TUE
4310612106 TUE
430123633 TUE
431061516 TUE
43012340126 TUE
4310613212 TUE
430123536 TUE
4310612212 TUE
430123526 TUE
430123516 TUE
431093133 TUE
431093333 TUE
.


sheet 2


Condition 1Condition 2Concatenate
430123406 TUE, 4 TUE, 6 TUE
430123446 TUE, 4 TUE
4310921
43012363 TUE, 3 TUE
43012356 TUE, 6 TUE, 6 TUE
.

1742100095057.png
 
Upvote 0
Sheet 2
Book1
ABC
1Condition 1Condition 2Concatenate
2430123406 TUE, 4 TUE, 6 TUE
3430123446 TUE, 4 TUE
44310921 
543012363 TUE, 3 TUE
643012356 TUE, 6 TUE, 6 TUE
Sheet2
Cell Formulas
RangeFormula
C2:C6C2=IFERROR(TEXTJOIN(", ",,TRANSPOSE(CHOOSECOLS(UNIQUE(FILTER(HSTACK(Sheet1!$H$2:$H$24,Sheet1!$I$2:$I$24,Sheet1!$J$2:$J$24,Sheet1!$U$2:$U$24),(A2=Sheet1!$H$2:$H$24)*(B2=Sheet1!$I$2:$I$24))),4))),"")


Sheet 1
Book1
HIJTU
1col HCol ICol JCol U
24301234056 TUE
34301234456 TUE
4430123356 TUE
54301234044 TUE
64301234434 TUE
7431093133 TUE
8430123633 TUE
9431061216 TUE
10431093233 TUE
11430123643 TUE
12431061417 TUE
13430123526 TUE
144310612106 TUE
15430123633 TUE
16431061516 TUE
1743012340126 TUE
184310613212 TUE
19430123536 TUE
204310612212 TUE
21430123526 TUE
22430123516 TUE
23431093133 TUE
24431093333 TUE
Sheet1
 
Upvote 0
Solution
AhoyNC said:
Sheet 2
Book1
ABC
1Condition 1Condition 2Concatenate
2430123406 TUE, 4 TUE, 6 TUE
3430123446 TUE, 4 TUE
44310921 
543012363 TUE, 3 TUE
643012356 TUE, 6 TUE, 6 TUE
Sheet2
Cell Formulas
RangeFormula
C2:C6C2=IFERROR(TEXTJOIN(", ",,TRANSPOSE(CHOOSECOLS(UNIQUE(FILTER(HSTACK(Sheet1!$H$2:$H$24,Sheet1!$I$2:$I$24,Sheet1!$J$2:$J$24,Sheet1!$U$2:$U$24),(A2=Sheet1!$H$2:$H$24)*(B2=Sheet1!$I$2:$I$24))),4))),"")


Sheet 1
Book1
HIJTU
1col HCol ICol JCol U
24301234056 TUE
34301234456 TUE
4430123356 TUE
54301234044 TUE
64301234434 TUE
7431093133 TUE
8430123633 TUE
9431061216 TUE
10431093233 TUE
11430123643 TUE
12431061417 TUE
13430123526 TUE
144310612106 TUE
15430123633 TUE
16431061516 TUE
1743012340126 TUE
184310613212 TUE
19430123536 TUE
204310612212 TUE
21430123526 TUE
22430123516 TUE
23431093133 TUE
24431093333 TUE
Sheet1
Click to expand...
AMAZING!! THIS WORKSSS! THANK YOU SO MUCH @AhoyNC
 
Upvote 0
You're welcome.
We can shorten the formula a little bit as the TRANSPOSE function is not need with the TEXTJOIN function.
See below.
Sheet 2
Book1
ABC
1Condition 1Condition 2Concatenate
2430123406 TUE, 4 TUE, 6 TUE
3430123446 TUE, 4 TUE
44310921 
543012363 TUE, 3 TUE
643012356 TUE, 6 TUE, 6 TUE
Sheet2
Cell Formulas
RangeFormula
C2:C6C2=IFERROR(TEXTJOIN(", ",,CHOOSECOLS(UNIQUE(FILTER(HSTACK(Sheet1!$H$2:$H$24,Sheet1!$I$2:$I$24,Sheet1!$J$2:$J$24,Sheet1!$U$2:$U$24),(A2=Sheet1!$H$2:$H$24)*(B2=Sheet1!$I$2:$I$24))),4)),"")
 
Upvote 0
AhoyNC said:
You're welcome.
We can shorten the formula a little bit as the TRANSPOSE function is not need with the TEXTJOIN function.
See below.
Sheet 2
Book1
ABC
1Condition 1Condition 2Concatenate
2430123406 TUE, 4 TUE, 6 TUE
3430123446 TUE, 4 TUE
44310921 
543012363 TUE, 3 TUE
643012356 TUE, 6 TUE, 6 TUE
Sheet2
Cell Formulas
RangeFormula
C2:C6C2=IFERROR(TEXTJOIN(", ",,CHOOSECOLS(UNIQUE(FILTER(HSTACK(Sheet1!$H$2:$H$24,Sheet1!$I$2:$I$24,Sheet1!$J$2:$J$24,Sheet1!$U$2:$U$24),(A2=Sheet1!$H$2:$H$24)*(B2=Sheet1!$I$2:$I$24))),4)),"")
Click to expand...
this formula works as well! Thank you very much @AhoyNC
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA help - Locking & unlocking on certain times of day
2
Replies
17
Views
860
dmt32
D
K
  • Question Question
Trying to make a formula to figure out what total hours in the month will be, based on what I already know
Replies
1
Views
163
Sufiyan97
Sufiyan97
C
  • Solved
Calculate hour total for each week
2
Replies
16
Views
411
CaptainGravyBum
C
Sandeep Warrier
  • Question Question
Calculate No. of Working Days based on Shift Pattern
Replies
2
Views
417
Tetra201
Tetra201
A
  • Question Question
Add a specified number of working hours to a date & time, given working day start, end and break times and holidays
Replies
2
Views
1K
AquaBuoy
A

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