pulling unique records from one workbook to another

**paintballlovr** · 06-15-2012, 11:40 AM

I'm not sure if this is possible, but if it is, this group is the one to ask. What I want to do is look at column a on speadsheet wm_load_sheet and move a list of the unique entries to column a of first sheet(data) on spreadsheet wm_load_sheet_template.

wm_load_sheet_template.xls wm_load_sheet.XLS

**tigeravatar** · 06-15-2012, 12:14 PM

paintballlovr,

The formula requires that you have headers in row 1, so:
In workbook wm_load_sheet_template, sheet 'data', insert a new top row. You can leave it blank if you'd like, but I recommend having headers.
Then in that sheet cell A2, use this formula and copy down (adjust the file path location of the other workbook as needed):

Please Login or Register  to view this content.

When you start getting #N/A errors, you're out of unique values. If there are more than 1000 rows filled with data in the [wm_load_sheet] workbook, adjust the range to look farther than $A$1:$A$1000. If you don't want the header from that workbook, adjust the start row from $A$1:$A$1000 to $A$2:$A$1000.

**paintballlovr** · 06-15-2012, 02:29 PM

Thank you for the response. I tried the formula and it does pull the data from column a of sheet wm_load_sheet.xls, but i still get duplicate results on column a of sheet"data" in workbook wm_load_sheet_template.xls. Did I do something wrong on the modified code listed below or will this not suppress duplicates in its current format?:

=INDEX(wm_load_sheet.XLS!$A2:$A$1000,MATCH(0,INDEX(COUNTIF($A$1:A1,wm_load_sheet.XLS!$A$1:$A$1000),),0))