|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
||||
|
||||
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")) 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. |
#3
|
|||
|
|||
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.
|
#4
|
||||
|
||||
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 =ConcatRows(A1:A15,D1:D4) or: =ConcatRows(A1:A15,{2,4,6,8}) No checks or bells and whistles. |
#5
|
|||
|
|||
That worked great, Thank you for the help.
|
#6
|
||||
|
||||
|
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 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 |