Search for Specific Value - For Every Match, Combine into One Cell.

[FrEaK_aCcIdEnT]

I have been trying to figure this out. I need to search a spreadsheet for a record. Then for every cell that it find with the record, offset to locate the corresponding data, Then repeat until it has found all records and combine them all into one cell as the output.

Searching, offset, setting the variable for a single record, is not problem. Just never tried to put multiple records together before.

Search for all "1" values and every record that matches value & char(10) & value. Could be 1 hit, could be 20 hits. Just need them all to end up in the same cell with a page break. Any takers?

 

[awoohaw]

what version of excel do you have? Please update your user profile so it shows up on your chat button.
Thanks in advance?

Do you want it with the line breaks?

 

[FrEaK_aCcIdEnT]

what version of excel do you have? Please update your user profile so it shows up on your chat button.
Thanks in advance?

Do you want it with the line breaks?

Sorry about that, It is updated now.

 

[dreid1011]

See if this does what you need:

Private Sub ComboNumVal()
Dim i As Long
Dim output As String

For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    If Range("A" & i) = 1 Then
        output = output & Chr(10) & Range("B" & i)
    End If
Next i

output = Replace(output, Chr(10), "", , 1)
Range("B15") = output
End Sub

Book1
	A	B
1	Col1	Col2
2	1	This
3	1	Is
4	1	What
5	2	This
6	2	How
7	2	Me
8	1	I
9	1	Need
10	5	You
11	6	Too
12
13
14
15		This Is What I Need
Sheet3

 

[awoohaw]

Here is a formula version:

HEre is a 365 version:

Book1
	A	B	C
1
2	1	This
3	1	Is
4	1	What
5	2	This
6	2	How
7	2	Me
8	1	I
9	1	Need
10	5	You
11	6	Too
12			This Is What I Need
Sheet3

Cell Formulas
Range	Formula
C12	C12	=TEXTJOIN(CHAR(10),1,FILTER($B$2:$B$11,$A$2:$A$11=1))

 

[alansidman]

an alternative is to use Power Pivot and build a measure

Book5
	A	B	C	D	E
1	Col1	Col2		Row Labels	Concat2
2	1	this		1	this is what I Need
3	1	is		2	this How Me You Too
4	1	what
5	2	This
6	2	How
7	2	Me
8	1	I
9	1	Need
10	2	You
11	2	Too
Sheet1

 

[FrEaK_aCcIdEnT]

See if this does what you need:

Private Sub ComboNumVal()
Dim i As Long
Dim output As String

For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    If Range("A" & i) = 1 Then
        output = output & Chr(10) & Range("B" & i)
    End If
Next i

output = Replace(output, Chr(10), "", , 1)
Range("B15") = output
End Sub

Book1
	A	B
1	Col1	Col2
2	1	This
3	1	Is
4	1	What
5	2	This
6	2	How
7	2	Me
8	1	I
9	1	Need
10	5	You
11	6	Too
12
13
14
15		This Is What I Need
Sheet3

It's my fault for not being 100% clear. I thought I could get away with using 1, 2, 3 in column A as the example. Here is by actual code. VIN is declared properly. Using it all throughout the module. I am trying to find the VIN in L over and over. Then pull the data from AC that corresponds with VIN match. I am getting a type mismatch error on the line below. I have tried .value, .text, and nothing added. All same result. I know it's probably something simple, but my brain is fried...

 

[FrEaK_aCcIdEnT]

Got it... &