Nested VLOOKUP Not counting Rows with Duplicate records

v127370

New Member
Joined
Jun 28, 2011
Messages
1
Hello Everyone,

This is my first time posting to a forum to get help with an Excel problem so thanks for the replies in advance.

I am having a problem with a nested vlookup formula not counting all records. Below is the formula I am using to gather all records:

=IF(ISNA(VLOOKUP(CONCATENATE(P$5,$A7),'WBN Data'!$A:$D,4,0))=TRUE,0,VLOOKUP(CONCATENATE(P$5,$A7),'WBN Data'!$A:$D,4,0))

I am using IE to export a list of data (in excel format) with common headers, which I then sort and count using access. The output of my query looks like this:

Disp Center /WorkCenter WBN Class CountOfDisp Center /WorkCenter WCG
FJ DV 2 LARGO
FJ DV/E 2 LARGO
FJ T/E 1 LARGO
FJ TD 1 SEMINOLE
FJ TDV 7 LARGO
FJ TDV 8 SEMINOLE
FJ TDV/E 1 SEMINOLE
FJ TV 1 LARGO
FJ V/E 1 SEMINOLE

I have a sheet with the formula listed above to read what type of record by location (FJ in this example), i.e. "WBN CLASS" and how many occurrances of each record in the "WBN CLASS" exist.

**I have discovered that the formula I am using is only capturing the first record of the same type. here is the formula:

Basically the problem is as follows, if "DV" is listed one time, the formula sorts and counts it fine. However, if the record occurs more than one time. e.g. "TDV" under "WBN CLASS", then the formula will only count the first record, and discard the valus associated with he second row of data with the same "WBN CLASS" identifier

Im sure I just have to modify my existing formula somehow to have it see the repeated, or common instances when I dump the data. Any help would be greatly appreciated.

Thanks again for anything you all can do.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
What's in cell P5?
What results would you have from example provided?

Id A7 is FJ and P5 TDV use this formula
=SUMPRODUCT(--(A2:A10=A7),--(B2:B10=P5)*C2:C10)

to get 15.
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,711
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top