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.
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.