Replace every occurrence of "ABC" with "123", "DEF" with "456 within a column

PETERFT73 · Feb 20, 2018

OK, I'm trying to add one final piece to an NBA spreadsheet I've been working on. On a separate sheet I have the name of every team, along with the average points they give up to each position. It looks kind of like this;
Column G contains B:F in that order.

[TABLE="width: 1000"]
<tbody>[TR]
[TD][TABLE="width: 653"]
<colgroup><col span="7"><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]PG[/TD]
[TD]SG[/TD]
[TD]SF[/TD]
[TD]PF[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PHO[/TD]
[TD]50[/TD]
[TD]43.2[/TD]
[TD]40.6[/TD]
[TD]45.5[/TD]
[TD]51.5[/TD]
[TD][/TD]
[TD]50 43.2 40.6 45.5 51.5[/TD]
[/TR]
[TR]
[TD]ORL[/TD]
[TD]49.1[/TD]
[TD]43[/TD]
[TD]39.8[/TD]
[TD]43[/TD]
[TD]50.9[/TD]
[TD][/TD]
[TD]49.1 43 39.8 43 50.9[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody></tbody>[/TABLE]
In my template the Rows containing the player's position and the opponent that day are adjacent. I'm hoping someone can help me figure out how get '50' to appear in a cell when "PHO" populates the opponent column, and "PG" populates the Position column.
I'm not asking for someone to write a Macro for me, I'm hoping this is a one liner. If it's not, I'll go back to the drawing board.
Thanks!!

jproffer · Feb 20, 2018

Your title is kinda strange, but if I'm understanding you right, this is what you're trying to do. If this UDF will help you, you're welcome to it.

Code:

Function CrossLookUp(ColFind As String, RowFind As String, TableArray As Range)

Dim clmn As Variant
Dim rw As Variant
Dim rwadd As Integer
Dim coladd As Integer

With TableArray

    Set clmn = .Find(ColFind, LookIn:=xlValues)
        
    Set rw = .Find(RowFind, LookIn:=xlValues)
        
        
    rwadd = rw.Row '- .Row + 1
    coladd = clmn.Column '- .Column + 1
    
 End With
    

CrossLookUp = Cells(rwadd, coladd)
    
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use your UDF just like it was a built-in Excel function. For example,

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

(Thanks to Rick R for the UDF instructions)

Excel 2013/2016

	A	B	C	D
PG	SG	SF	PF	C
PHO
ORL

Opponent	PHO
Position	SF

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]43.2[/TD]
[TD="align: right"]40.6[/TD]
[TD="align: right"]45.5[/TD]
[TD="align: right"]51.5[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]49.1[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]39.8[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]50.9[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]40.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D5[/TH]
[TD="align: left"]=CrossLookUp($B$6,$B$5,$A$1:$E$3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

PETERFT73 · Feb 23, 2018

Thank you very much jproffer. I will try that as soon as I get home.

jproffer · Feb 23, 2018

Sounds good. In the mean time, does that look like what you're wanting? If not, I'll try again. The above function basically finds the value at the intersection of a column and a row. In this case, the column with SF in it (column D) and the row with PHO in it (row 2). It works very similar to vLookup.

Replace every occurrence of "ABC" with "123", "DEF" with "456 within a column

PETERFT73

New Member

Excel Facts

jproffer

Well-known Member

PETERFT73

New Member

jproffer

Well-known Member

Similar threads

Forum statistics

Share this page

Replace every occurrence of "ABC" with "123", "DEF" with "456 within a column

PETERFT73

New Member

Excel Facts

jproffer

Well-known Member

PETERFT73

New Member

jproffer

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock