VBA: Binary VlookUp against XLSX or XLS

[unknownymous]

Hello Gurus,

I have a Binary excel file and want to update a particular column using the unique identifier from an xlsx or xls (get open file) . Do you think it is feasible?

Example Binary Sheet:

Number	Name	Country	Note
001	Mark	Finland	Checked
002	Sean	United States

Example: LookUp Reference Sheet (XLSX or XLS)

Number	Country	Note
001	Nordic	Verified
002	North America	Verified

Basically, The country in the Binary Sheet will be replaced based on the Number Identifier from the reference sheet. Finland to Nordic and United States to North America.


Appreciate the help.

 

[jdellasala]

I don't think you mean that you have a Binary file. That's not possible within Excel alone. Power Query will identify files as a Binary File when importing a folder, but that still can't be used in Excel.
The column that has the header "Number" is not a number. It's the text "001" and "002". Unless you force it by formatting it as 000.

Book1
	A	B
1	Number
2	001	<- Cell Format is 000. Cell value is 1
3	002	<- Cell Format is 000. Cell value is 2
Sheet1

Those are numbers with a custom format of 000. There is no alignment formatting, so you know they're numbers since they're right justified. If they were text, they'd look like this:

Maybe this illustrates the problem better:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Number	Value		Number	Vlookup #	Vlookup #		NOT Number	Value		Vlookup Txt	Returned	Vlookup Txt
2	001	Row 2		1	Row 2	#N/A		001	Row 2		1	#N/A	001	Row 2
3	002	Row 3		2	Row 3	#N/A		002	Row 3		2	#N/A	002	Row 3
Sheet1

Cell Formulas
Range	Formula
E2	E2	=VLOOKUP(D2,A1:B3,2,0)
F2	F2	=VLOOKUP(D2,H1:I3,2,0)
E3	E3	=VLOOKUP(D3,A1:B3,2,0)
F3	F3	=VLOOKUP(D3,H1:I3,2,0)
L2:L3,N2:N3	L2	=VLOOKUP(K2,$H$1:$I$3,2,0)

As for the automatic numbering, I suggest you convert the data into a Table like this:

Book1
	A	B	C	D
1	Number	Name	Country	Note
2	001	Mark	Finland	Checked
3	002
Sheet2

Cell Formulas
Range	Formula
A2:A3	A2	=IF(ISNUMBER(A1),A1+1,1)

Now whenever you hit [Tab] in the last row of the Note column, a new row will be created and automatically generate the next number. Column A has a custom format of 000. The table will also extend if you enter anything into columns B-D. Just don't enter anything in Column A, the table will automatically copy the formula down in Column A retaining the custom format.

 

[MARK858]

I don't think you mean that you have a Binary file. That's not possible within Excel alone.

I think the OP just means they have an .xlsb file