I have a data set SalesList with 4 columns: FName, MName, LName, Zone
Each zone has a worksheet with 4 columns: Seq#, LastName, FirstName, Zone
For each zone I create a separate worksheet that pulls down all names (FName&LName) from SalesList that are in the same zone, to eliminate transcription errors.
Zone names can be alphanumeric: e.g., Neptune, Panda, Puma, Kingsman, 1st, 2nd, …25th
Every zone has between 8 and 30 names, but some can be max 50.
My array formula for
last name: {=IFERROR(IF(COUNTIF(SalesList!$D$2:$D$92,$D$1) < ROWS($B$2:B2), "", INDEX(SalesList!$C$2:$C$92, SMALL( IF(SalesList!$D$2:$D$92 =$D$1, ROW( SalesList!$D$2:$D$92)), ROW(SalesList!D2)))),"")}
first name: =IFERROR(IF(COUNTIF(SalesList!$D$2:$D$92,$D$1) < ROWS($B$2:C2), "", INDEX(SalesList!$A$2:$A$92, SMALL( IF(SalesList!$D$2:$D$92 =$D$1, ROW( SalesList!$D$2:$D$92)), ROW(SalesList!D1)))),"")
Note: $D$1 in above formula refers to the title row which takes its name from the filename of the worksheet: The formula in $D$1: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
I did try to replace the formula by typing in the name of the worksheet, no difference.
These two formulas do pickup and populate exact number of names in the data set with corresponding zones, but it's a mish/mash of results. worksheets.
Every zone is missing the first name in the zone.
Every zone lists the last Name from the next zone, i.e., the Neptune zone has a Name from Panda and is listed as Neptune. The Panda zone has a Name from Puma and is listed as Panda.
Starting with the Panda worksheet, every Name listed first in the zone worksheet list, even though it is the second Name in the SalesList, has the wrong last name.
For third Name, It picks up the correct first name, but picks up the last name of the second Name following listed in the zone worksheet, i.e., picks up the third Name’s last name from the SalesList worksheet.
Is there a better alternative to array formulas that can be used in this case?
Any help or suggestion would be very much appreciated.
Regards,
JC
Bookmarks