VBA Matching value in a range and return other cell values

[Jasesair]

I didn't think this would be difficult...but perhaps I'm not thinking clearly.

I have values in cells H8:K8 from sheet 'Data Entry'. In sheet 'Results', I'm wanting to match A1 with A1's unique value in column F (it's long but if a range is needed, then F3:F6000). Once matched, I'd like the H8:K8 'Data Entry' cells to be returned in column H:K in the matched row in the 'Results' sheet. I hope that's making sense - I'm sure I could have explained that far better!!!

Is this possible with a little VBA?

 

[Peter_SSs]

Is this possible with a little VBA?

It should be possible without vba. Is that acceptable?

Is this what you mean? If not, instead of us guessing the data and layout, could you give us a small set of dummy data and the expected results with XL2BB so that we know exactly what you have, where and what you want, and where?

Jaseair.xlsm
	F	G	H	I	J	K
1
2
3	a		803	326	933	389
4	b		256	869	559	838
5	c		932	426	798	439
6	d		977	539	653	505
7	e		813	746	184	154
8	f		872	457	152	906
9	g		913	254	488	330
10	h		987	530	494	244
11	i		987	563	296	633
12	j		582	575	268	572
13	k		890	782	272	791
14	l		990	793	844	135
15	m		258	825	483	575
16	n		348	756	246	784
17	o		424	858	199	598
18	p		298	644	968	592
19	q		857	869	365	914
Data Entry

Jaseair.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	f							872	457	152	906
2	c							932	426	798	439
Results

Cell Formulas
Range	Formula
H1:K2	H1	=INDEX('Data Entry'!H$3:H$6000,MATCH($A1,'Data Entry'!$F$3:$F$6000,0))

 

[Jasesair]

I liked the idea of index-match but I can't even get that to work. The answer (index) is simply going to be H8 from the 'Data Entry' sheet in this case. My other issue with index-matching is the document is going to be huge if that's the method I go with. Just in this case alone, I'd have to have about 24000 index-match formulas. I'm not sure how to use XL2BB. I'll have a play though.

 

[Jasesair]

I think with the volume of index-match formulas needed, it's going to be far better to run a macro. Is this a possibility?

 

[Peter_SSs]

It sounded like my results shown in post #2 are not what you wanted so can you show us some sample data and expected results? It would be pointless developing code that does not produce the results you want.

 

[Jasesair]

I wasn't able to download XL2BB at work so it has to be images. I'm essentially trying to transpose the values in Data Entry into the Results sheet, as one row, all based on the unique match of A1 in column F. Each row of values in Data Entry would ideally transpose over into the correct Results row.

 

[Peter_SSs]

I'm essentially trying to transpose the values in Data Entry into the Results sheet, as one row, all based on the unique match of A1 in column F. Each row of values in Data Entry would ideally transpose over into the correct Results row.

Like this?

Sub ToOneRow()
  Dim Results As Variant
  Dim rngData As Range, rngFound As Range
 
  With Sheets("Data Entry")
    Set rngData = .Range("H8:K" & .Range("H:K").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row)
  End With
  Results = Split(Join(Application.Transpose(Evaluate( _
            rngData.Columns(1).Address(External:=True) & "&""@""&" & _
            rngData.Columns(2).Address(External:=True) & "&""@""&" & _
            rngData.Columns(3).Address(External:=True) & "&""@""&" & _
            rngData.Columns(4).Address(External:=True))), "@"), "@")
  With Sheets("Results")
    Set rngFound = .Columns("F").Find(what:=.Range("A1").Value, LookAt:=xlWhole)
    If Not rngFound Is Nothing Then
      rngFound.Offset(, 2).Resize(, UBound(Results) + 1).Value = Results
    Else
      MsgBox .Range("A1").Value & " not found in column F"
    End If
  End With
End Sub

If unable to use XL2BB you can just copy from Excel to your post like this. At least that can be copied at this end to save what could be a lot of typing which many helpers might just bypass.

	H	I	J	K
8​	2​	3​	2​
9​		2​	1​
10​
11​			3​

 

[Jasesair]

Peter, this is so, so good!!! I've tested it heavily and the only thing that isn't working is K20 in 'Data Entry' isn't transposing to the 'Results' sheet. It should be appearing in column BG. Any ideas?

 

[Peter_SSs]

K20 in 'Data Entry' isn't transposing to the 'Results' sheet. It should be appearing in column BG. Any ideas?

Yes, my mistake, sorry - missed a "+1"

rngFound.Offset(, 2).Resize(, UBound(Results) + 1).Value = Results

 

[Jasesair]

That's perfect! Thanks again so much, and thanks for the advice with providing data for future posts.