VLOOKUP? Help please

[josh.clare]

Hello all,

I was wondering if someone could help/advise me.
I have a table in sheet1 with the following headings:

Company
Invoice Number
Paid Y/N

I have a table in sheet2 with the following headings:

Company
Total Invoices Paid
Total Invoices Unpaid

I want sheet2 (using formulas or a macro) to automatically fill its self in using the data from Sheet1.

Using the Company as the lookup, i then want it to count how invoices there are relating to that company and how many of them are paid/unpaid depending on the y (yes) or n (no) in the row next to it.

Hope this makes sence,
would really appreciate some help with this.

Thanks,
Josh

 

[repairman615]

I will have to get back to you on this tonight... gotta get going today on a few projects.

I am thinking the advance filter using the copy to range and unique records may do the trick...

 

[repairman615]

Hello Josh,

Using the advanced filter it is possible to filter uniques of the company names. The catch is that this may only be copied to a new location on the activesheet or same sheet.

Here is a workaround that may suit your needs:

In an unused column,

Create another table of 1 column on Invoice Details(sheet1). This should have the label Company at the top. Resize to make the table have many more rows than you feel are needed. This column may be hidden if you choose.

Select the data in the original table from Company, the label, down to the last entry.
Next use the advanced filter found on the data tab (2007),
Select copy to another location
In the copy to feild, enter your cell address of the label Company on the new table.
Select Unique Records Only and Press OK

Now hopefully there should be the uniques.
Last thing to do now is enter this formula into the table on Department Details sheet.
<TABLE style="WIDTH: 414pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=552><COLGROUP><COL style="WIDTH: 414pt; mso-width-source: userset; mso-width-alt: 10093" width=552><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 414pt; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl63 height=20 width=552>=IF(Table3[[#This Row],[Company]]<>0,Table3[[#This Row],[Company]],"-------")</TD></TR></TBODY></TABLE>

You may need to change Table3 to what your table name is.

You will need to filter periodically to refresh the unique list... if macros are ok this could happen automaitially with VBA.

Jeff