Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-25-2021, 12:16 AM
ganesang ganesang is offline Lookup and Textjoin help Windows XP Lookup and Textjoin help Office 2016
Competent Performer
Lookup and Textjoin help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default Lookup and Textjoin help

Hi Guys,

I need to lookup and get multiple values in to one cell using excel function.

Sheet 1 have data and column C need to get in sheet 2 column B using functions.

Please check the attachment and help me out



Thanks
Ganesan. G
Attached Files
File Type: xlsx Textjoin.xlsx (11.1 KB, 8 views)
Reply With Quote
  #2  
Old 08-25-2021, 02:30 AM
p45cal's Avatar
p45cal p45cal is online now Lookup and Textjoin help Windows 10 Lookup and Textjoin help Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

In cell B2:
Code:
=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Sheet1!$C$2:$C$31,Sheet1!$B$2:$B$31=A2,"None")))
copied down.
Reply With Quote
  #3  
Old 08-25-2021, 03:22 AM
ganesang ganesang is offline Lookup and Textjoin help Windows XP Lookup and Textjoin help Office 2016
Competent Performer
Lookup and Textjoin help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

Thanks much!!

I got the answer my question...
Reply With Quote
  #4  
Old 08-25-2021, 03:26 AM
p45cal's Avatar
p45cal p45cal is online now Lookup and Textjoin help Windows 10 Lookup and Textjoin help Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Code:
=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Sheet1!$C$2:$C$31,(Sheet1!$B$2:$B$31=A2)*(Sheet1!$A$2:$A$31="Q1"),"None")))
Reply With Quote
  #5  
Old 08-25-2021, 03:26 AM
ganesang ganesang is offline Lookup and Textjoin help Windows XP Lookup and Textjoin help Office 2016
Competent Performer
Lookup and Textjoin help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

Thank you so much!!!
Reply With Quote
  #6  
Old 08-25-2021, 03:54 AM
ganesang ganesang is offline Lookup and Textjoin help Windows XP Lookup and Textjoin help Office 2016
Competent Performer
Lookup and Textjoin help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

Sorry to ask you again

I have tried in real data but cannot pull exactly what i need or correct

Can you please check the attached file and correct me?
Attached Files
File Type: xlsx Textjoin.xlsx (20.2 KB, 6 views)
Reply With Quote
  #7  
Old 08-25-2021, 04:40 AM
p45cal's Avatar
p45cal p45cal is online now Lookup and Textjoin help Windows 10 Lookup and Textjoin help Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Can you highlight some of what's not 'what i need' and what's not correct?
Reply With Quote
  #8  
Old 08-25-2021, 04:43 AM
ganesang ganesang is offline Lookup and Textjoin help Windows XP Lookup and Textjoin help Office 2016
Competent Performer
Lookup and Textjoin help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

In sheet 2 Column C2 result should be RB3043, RB3043, RB3043,

But getting only one may be the same value. But i wanted to get these three

DO NOT ignore duplicated values and need to get that too.
Reply With Quote
  #9  
Old 08-25-2021, 04:49 AM
ganesang ganesang is offline Lookup and Textjoin help Windows XP Lookup and Textjoin help Office 2016
Competent Performer
Lookup and Textjoin help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

I got the answer when i have removed the UNIQUE function.

thanks very much
Reply With Quote
  #10  
Old 08-25-2021, 04:51 AM
p45cal's Avatar
p45cal p45cal is online now Lookup and Textjoin help Windows 10 Lookup and Textjoin help Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

I had assumed you didn't want repeating values in a list.
So take out the unique bit:
Code:
=TEXTJOIN(",  ",TRUE,FILTER(Sheet1!$C$2:$C$380,(Sheet1!$A$2:$A$380=A2)*(Sheet1!$B$2:$B$380=B2),"None"))
in cell C2 copied down.
Reply With Quote
  #11  
Old 08-25-2021, 04:54 AM
ganesang ganesang is offline Lookup and Textjoin help Windows XP Lookup and Textjoin help Office 2016
Competent Performer
Lookup and Textjoin help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

Sorry i forgot to tell you

Anyway thanks much for your time!!
Reply With Quote
  #12  
Old 08-26-2021, 04:07 AM
ganesang ganesang is offline Lookup and Textjoin help Windows XP Lookup and Textjoin help Office 2016
Competent Performer
Lookup and Textjoin help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

Hi I need your help again

Is there any possible to sumproduct the FILTER results numeric value.

Please find the attached and let me know we can sumproduct column D in sheet2
Attached Files
File Type: xlsx Textjoin.xlsx (22.0 KB, 5 views)
Reply With Quote
  #13  
Old 08-26-2021, 04:40 AM
p45cal's Avatar
p45cal p45cal is online now Lookup and Textjoin help Windows 10 Lookup and Textjoin help Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

For a given member in a given quarter you want separate total Hrs for each Eng?
Not easy using this path.

Are you sure it's not a pivot table you want?

See attached at cell G1, and another, slightly different arrangement at cell Q1. These take seconds to set up.
Attached Files
File Type: xlsx msofficeforums47554TextjoinPivots.xlsx (36.6 KB, 5 views)
Reply With Quote
  #14  
Old 08-26-2021, 05:27 AM
ganesang ganesang is offline Lookup and Textjoin help Windows XP Lookup and Textjoin help Office 2016
Competent Performer
Lookup and Textjoin help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

Hi thanks for the reply

Can we have sum using char(10) as below?

if multiple names with multiple values

column C column D
RB3043 7.5
Pearl15 1
BR715 1

or

if same name with multiple values


RB3043 290.75 (this is comes from three places)

Please advise
Reply With Quote
  #15  
Old 08-26-2021, 05:51 AM
p45cal's Avatar
p45cal p45cal is online now Lookup and Textjoin help Windows 10 Lookup and Textjoin help Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

As I said, not easily. The pivot table gives this information.
What version of Excel are you using?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
LOOKUP - Complex lookup with 2 lookups in 1 cell sglandon Excel 6 05-05-2016 09:44 AM
Lookup and Textjoin help Lookup Tony Singh Excel 3 03-06-2015 11:03 AM
Lookup angie.chang Excel 1 07-27-2012 09:45 PM
Lookup and Textjoin help Possible Lookup Karen222 Excel 3 01-10-2012 05:41 AM
Lookup and Textjoin help LookUp aztiguen24 Excel 5 05-24-2011 03:57 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:03 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft