Keeping track of search number references based on criteria <> N

[ellison]

We have Raw Data which we copy and paste via notepad into other information sources .
Search A goes through "Information Source A", Search B goes through "Information Source B", Search C goes through "Information Source C", etc etc
Search A is the quickest and easiest one to yield results, then Search B, then C then D.
So we start off with all of the Raw Data going through Search A, then review the results in order to cull some before sending the remainder Search B. We then repeat these steps for Search C & finally Search D.

The issue we have is that the searches don't give you back the specific search term that was searched for.
Instead they give the results and a reference i.e."Search 1 of 20", "Search 2 of 20" etc

What we'd like is to somehow work out what those references would be... (which would be the numbers in the grey cells in the sample below)

Hopefully some sample data will show what we are getting at, but the actual lists are about 1000 rows

Book1
	A	B	C	D	E	F
1	Raw Data	Reference Numbers for Search "A"	Include on Search B?	Reference Numbers for Search B	Include Search C?	Reference Numbers for Search D
2	alpha	1	N
3	beta	2		1	n
4	charlie_	3	N
5	delta	4		2		1
6	_echo	5	N
7	foxtrot	6		3	n
8	golf	7		4		2
9	hotel	8	n
10	india_	9		5	n
11	juliet	10	n
Sheet3

Cell Formulas
Range	Formula
B2:B11	B2	=ROW()-1

We can just about figure out how to get the search number for "Search A", but we are totally stumped once it comes to figuring out how to get the corresponding search numbers for the others.

Any help very, very much appreciated!

 

[ellison]

Hi I think I've found a workaround...
Get rid of the multiple columns where you review the information and decide whether to carry the search term on to the next search. Instead, only update Column B.
In Column C, put in a formula to give the Search Reference
Set the Excel preferences on calculating formula to manual.

So, first search is all 10 search terms:

Keeping-Track-Of-Search-Number-Refences-Based-On-Criteria.xlsx
	A	B	C
1	Raw Data	Include on next Search?	Helper To ID Search Reference
2	alpha		1
3	beta		2
4	charlie_		3
5	delta		4
6	_echo		5
7	foxtrot		6
8	golf		7
9	hotel		8
10	india_		9
11	juliet		10
Sheet3 (2)

Cell Formulas
Range	Formula
C2:C11	C2	=IF(B2="",COUNTBLANK($B$2:$B2),"")

Review the results and mark where you will "not" carry it forward:

Keeping-Track-Of-Search-Number-Refences-Based-On-Criteria.xlsx
	A	B	C
1	Raw Data	Include on next Search?	Helper To ID Search Reference
2	alpha	N	1
3	beta		2
4	charlie_	N	3
5	delta		4
6	_echo	N	5
7	foxtrot		6
8	golf		7
9	hotel	n	8
10	india_		9
11	juliet	n	10
Sheet3 (2)

Cell Formulas
Range	Formula
C2:C11	C2	=IF(B2="",COUNTBLANK($B$2:$B2),"")

Hit Save, that will now calculate the new "search References":

Keeping-Track-Of-Search-Number-Refences-Based-On-Criteria.xlsx
	A	B	C
1	Raw Data	Include on next Search?	Helper To ID Search Reference
2	alpha	N
3	beta		1
4	charlie_	N
5	delta		2
6	_echo	N
7	foxtrot		3
8	golf		4
9	hotel	n
10	india_		5
11	juliet	n
Sheet3 (2)

Cell Formulas
Range	Formula
C2:C11	C2	=IF(B2="",COUNTBLANK($B$2:$B2),"")

Now filter Column B for blanks cells in order to find the updated list of of terms to send through the next search:

Keeping-Track-Of-Search-Number-Refences-Based-On-Criteria.xlsx
	A	B	C
1	Raw Data	Include on next Search?	Helper To ID Search Reference
3	beta		1
5	delta		2
7	foxtrot		3
8	golf		4
10	india_		5
Sheet3 (2)

Cell Formulas
Range	Formula
C3,C5,C7:C8,C10	C3	=IF(B3="",COUNTBLANK($B$2:$B3),"")

Then you review the results & mark up which of those you will not be carrying forward.
-Hit Ctrl-S and you will have the new Search References.
-Filter Column B for non-blanks and you will have the latest updated list of search terms.

Repeat these steps until you get to the last search.