gathering values based off critieria and in turn forming a list

ok lets say you have thousands of rows of raw data and you are running vlookups to pull specific data from the raw data to update your spreadsheet. Your raw data consist of some new ideas so when you vlook up, you get an error. In my case i get about 50 errors all spaced out amoung the thousands of rows or raw data. I would like to create a formula that would go and find the error and output a code/value that already exist in the raw data and output that in Cell D1 for example. The it will go and find the second error and output that into cell D2 and then the third error and output that into D3 and so on.....

The idea is that when its done a list of all the errors will have been created so then i can use other formulas to simple add the new info to the workbook. Instead of search through thousands of rows for the errow.

Pivot tables are not an option at the moment or sorting of any kind.


thanks!

Or filtering - filter just the errors.

thank you for your post, but as i mentioned any form of sorting is not an option at the moment. I would like the excel sheet to reconize the errors and grab the identifier cell and put in a list fashion in another part of the excel sheet.

Almost as if you made a critieria for =CONCATENATE( to collect all the identifiers that have an error and CONCATENATE with a comma inbetween each item then some how seperate the string of data using another formula to pull the first item and then in the cell below that to put the second item and so on..... so it builds a list. then i can use the identifier once in the list to vlook up all the correct info and easily updat the sheet from there.

Make sense?

auto list.xlsx

Please use the attachment for a referance.

Thanks!

With formula, just for your sample file

Filtering is not sorting. Filter the errors, copy the filtered data (ie the new items) to another sheet and use that to add it into your main data to eliminate the errors.

Originally Posted by azumi

With formula, just for your sample file

Just what i was looking for!

thanks!

azumi,

Again thank you for this formula. I have used it many times. I've used it so much in a wookbook that it made calculations take 30 mins.... Do you have a way of doing the same task but in VBA purhaps?

Change to this, more faster than previos formula:

Non Array Formula:
=IFERROR(INDEX(B$8:B$19,AGGREGATE(15,6,(ROW($B$8:$B$19)-MIN(ROW($B$8:$B$19))+1)/($E$8:$E$19="Error"),ROWS($A$1:$A1))),"")

just ENTER and copy cross and down