Formula or Macro to Extract Account Numbers

[howard]

I have account numbers in Col D in Sheet TB and decriptions in Col G

I would like a formula or macro to extract the account numbers where the description New Car Sales appears in Col G on Sheet Extract

Your assistance in this regard is most appreciated

 

[hiker95]

howard,

It would be appreciated if your macro will ignore all errors
Col G such as N/A#

Can we have another screenshot of the raw data in worksheet TB that contains the #N/A formulae?

There is a setting in Excel Jeanie that you can click on/set to display formulae.

 

[howard]

Hi Hiker95

I don't have Excel Jeannie HTML on my Home PC. I tried downloading this and then selecting the ADD-ON Button but the add-ons do not appear in the ribbon. Please advise how I can activate the Excel Jeannie, otherwise I will copy the raw data tomorrow

 

[hiker95]

howard,

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel.com | Excel Resources | Excel Seminars | Excel Products

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[howard]

Hi Hiker95

Thanks for the info and all your assistance. I will post a screen shot tomorrow as I never saved the file on my portable hard drive

 

[hiker95]

howard,

Thanks for the feedback.

You are very welcome. Glad I could help.

I will post a screen shot tomorrow

See you then.

 

[howard]

Hi Hiker95

See Screen shot below showing #N/A

Excel Workbook
	D	E	F	G
1	9101Z	0.00	0.00	#N/A
Sheet1

 

[hiker95]

howard,

The screenshot is good, but, Excel Jeanie allows you to change a setting, and, display a formula.

What is the formula that is in cell G1?

 

[hiker95]

howard,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub GetNewCarSalesV2()
' hiker95, 03/12/2014, ME763249
Dim c As Range, n As Long, o As Variant, i As Long, nr As Long
Application.ScreenUpdating = False
With Sheets("TB")
  n = Application.CountIf(.Columns(7), "NEW CAR SALES")
  If n = 0 Then
    MsgBox "There are no 'NEW CAR SALES' in column G - macro terminated!"
    Exit Sub
  ElseIf n > 0 Then
    ReDim o(1 To n, 1 To 1)
    For Each c In .Range("G2", .Range("G" & Rows.Count).End(xlUp))
      On Error Resume Next
      If c = "NEW CAR SALES" Then
        i = i + 1
        o(i, 1) = c.Offset(, -3)
      End If
      On Error Goto 0
    Next c
  End If
End With
With Sheets("Extract")
  nr = .Cells(Rows.Count, 7).End(xlUp).Row + 1
  .Cells(nr, 7).Resize(UBound(o, 1)) = o
  .Columns(7).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetNewCarSalesV2 macro.

 

[howard]

Hi Hiker95

Thanks for the help, much appreciated

 

[hiker95]

howard,

You are very welcome. Glad I could help.

And, come back anytime.