0

I need help with a formatting issue in Excel, which is interfering with the VLOOKUP function in my Excel sheet.

I have two sheets with more than 20,000 column values as lookup, and the same number of values for reference. All the values in both cells are weirdly formatted, some with green triangles at the upper left corner of cells, some are text, etc.

Is there a way in Excel using a macro/VBA to remove or make all formatting similar in both sheets? The reason for VBA is because the person who is going to work with this file needs everything automated and is not familiar with Excel at all. I already have the VLOOKUP function in the cells, I just need to work with the formatting.

Gaffi
  • 4,307
  • 8
  • 43
  • 73
Nupur
  • 347
  • 8
  • 19
  • 36

2 Answers2

2

Well, I fight with partial lookups this way:

In the items array, I create new empty FIRST column and then place formula

="+"&B2

This will take the content of Cell B2 and add + in the front of it.

When I do vlookup, I add "+" to searcheable value

=VLOOKUP("+"&A6,A:O,2,FALSE)

Therefore, instead of comparing for example Strings and numbers, I compare Strings, by adding "+" in the front.

Another technique, is to kill all formatting: Select whole column, click DATA-TEXT TO COLUMNS-DELIMITED and then DESELECT ALL DELIMITERS. Click Finish. This will clear your formatting.

===========================================================================

This is the VBA solution you asked for:

Call it from Excel

=GetLookup(G2,A:C)

Here is VBA:

Function GetLookup(LOOKFOR As String, RANGEARRAY As Range) As String
  GetLookup = Application.WorksheetFunction.VLookup("+" & LOOKFOR, RANGEARRAY, 3, False)
End Function

Good luck!

Andrew
  • 7,619
  • 13
  • 63
  • 117
  • @ Andrew: Thanks for your response. This worked absolutely fine. I just need one more help, sorry if I am asking much. Would it be possible to do this "+" and vlookup function with a macro/VBA? – Nupur Mar 14 '12 at 15:50
  • @Gaffi: Thanks for your response. The code is giving me an error. When I debug it it highlights the following lines: YourSheet1.Range("A1").FormatConditions = YourSheet2.Range("A1").FormatConditions YourSheet1.Range("A1").Font = YourSheet2.Range("A1").Font YourSheet1.Range("A1").Borders = YourSheet2.Range("A1").Borders ' Set the color/pattern of the cell YourSheet1.Range("A1").Interior = YourSheet2.Range("A1").Interior – Nupur Mar 14 '12 at 15:52
  • @Nupur I added VBA to my post – Andrew Mar 14 '12 at 16:51
  • @ Andrew: the code is working fine. The only problem I have is when the lookup function copies a date value from the sheet, its gets converted to a weird number eg: 401289 which is usual I suppose. but when I try to change the formatting of this value to a date sormat, it remains the same as 401289. can you tell me how to fix this? – Nupur Mar 16 '12 at 20:53
1

I'm assuming the data type in all of the cells is the same, or you want it to all be the same. The following steps will make the cells a uniform type:

  1. Save your workbook, in case this does not do as you require
  2. Select all cells you wish to be of the same cell type
  3. Press Ctrl+1, on the "Number" tab, select the type you wish these cells to take. Press OK.
  4. Open the VBA editor using Alt+F11
  5. Open the immediate window with Ctrl+G
  6. Type the following: for each cell in selection : cell.formula = cell.value : next cell
  7. Press enter (you may have to wait a few seconds).

If you take this action with the same data type (e.g. choose "Text" for both ranges in step 3) on both your ranges you should be "comparing apples with apples" and your VLOOKUP should function as required.

Hope this helps.

Edit: formatting, clarification

mkingston
  • 2,678
  • 16
  • 26