Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-15-2021, 12:31 PM
BoardingSUP BoardingSUP is offline Copying and Concatenating data in excel based on a value in another column Windows 10 Copying and Concatenating data in excel based on a value in another column Office 2019
Novice
Copying and Concatenating data in excel based on a value in another column
 
Join Date: Oct 2021
Posts: 3
BoardingSUP is on a distinguished road
Default Copying and Concatenating data in excel based on a value in another column

I am working to get data from one sheet and several different rows based on a number in a separate column, but then the data from other column to concatenate and be pasted into a specific cell on sheet 2. For example below, I want to look for numbers 1,5,6,9 on in column B, then select the text from Column A from those rows that contain 1,5,6,9 in column B, Concatenate it and paste in in a specific cell lets say Cell Sheet 2 Cell B6. I was able to write a code that is specifically going to the cells I want and doing it, but I need it slightly dynamic to parse for that number in column B then pull from column A concatenate or "merge" the text and place it in Sheet2. One of the issues I was having was with regards to the range not being consistent and it needing to be unionized. Thanks for the help.


Even like a for loop that runs through each of the numbers 1,5,6,9 and if there is nothing skips and then if there is text copies it into the cell in sheet 2, I am not sure if that would work though or how I can run a for loop through those specific numbers?
Reply With Quote
  #2  
Old 10-16-2021, 07:17 AM
p45cal's Avatar
p45cal p45cal is offline Copying and Concatenating data in excel based on a value in another column Windows 10 Copying and Concatenating data in excel based on a value in another column 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

FILTER and TEXTJOIN available to you?
Code:
=TEXTJOIN(" ",TRUE,FILTER(Sheet1!A1:A15,ISNUMBER(MATCH(Sheet1!B1:B15,{1,5,6,9},0)),"Not found"))
the numbers 1,5,6,9 in a range K7:K10:
Code:
=TEXTJOIN(" ",TRUE,FILTER(Sheet1!A1:A15,ISNUMBER(MATCH(Sheet1!B1:B15,K7:K10,0)),"Not found"))

If this is too simplistic, supply a workbook with what you're wanting rather than a picture of one.
Reply With Quote
  #3  
Old 10-16-2021, 10:34 AM
BoardingSUP BoardingSUP is offline Copying and Concatenating data in excel based on a value in another column Windows 10 Copying and Concatenating data in excel based on a value in another column Office 2019
Novice
Copying and Concatenating data in excel based on a value in another column
 
Join Date: Oct 2021
Posts: 3
BoardingSUP is on a distinguished road
Default

Thank You for the help, it by any means is not too simple, but I do not have filter function available as that is only on 365 at this point.
Reply With Quote
  #4  
Old 10-17-2021, 05:06 AM
p45cal's Avatar
p45cal p45cal is offline Copying and Concatenating data in excel based on a value in another column Windows 10 Copying and Concatenating data in excel based on a value in another column 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

You can try a function like this:
Code:
Function ConcatRows(sce, WhichRows)
For Each rw In WhichRows
  Z = Z & sce.Cells(rw).Value
Next rw
ConcatRows = Z
End Function
and use it in a worksheet thus:
=ConcatRows(A1:A15,D1:D4)
or:
=ConcatRows(A1:A15,{2,4,6,8})


No checks or bells and whistles.
Reply With Quote
  #5  
Old 10-17-2021, 06:25 AM
BoardingSUP BoardingSUP is offline Copying and Concatenating data in excel based on a value in another column Windows 10 Copying and Concatenating data in excel based on a value in another column Office 2019
Novice
Copying and Concatenating data in excel based on a value in another column
 
Join Date: Oct 2021
Posts: 3
BoardingSUP is on a distinguished road
Default

That worked great, Thank you for the help.
Reply With Quote
  #6  
Old 10-17-2021, 08:05 AM
p45cal's Avatar
p45cal p45cal is offline Copying and Concatenating data in excel based on a value in another column Windows 10 Copying and Concatenating data in excel based on a value in another column 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

fyi:
Excelguru Help Site - A message to forum cross posters
Reply With Quote
Reply

Tags
excel 2019, vba

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto populate form (data from excel) in Word based on drop down list selection (data from excel) wvlls Word VBA 1 03-22-2019 02:29 PM
Copying and Concatenating data in excel based on a value in another column Copying data in a column to another column Peace Freak Excel 2 04-28-2017 12:31 AM
Extract data based on pattern with respect to specific column PRA007 Excel Programming 14 12-04-2015 04:32 AM
Help with Macro - copying formula to last line of data based on another column kidwispa Excel Programming 1 07-12-2015 05:01 PM
Copying data from Project to Excel OTPM Project 13 12-20-2013 10:25 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:06 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