Im at a loss - maybe one of you have done something like this before:

I have data in a table that I need to flag with a X.
The data I need flagged is for each unique value in column A I need the 1st, 3rd, 5th and then every 5 after that from column B until I reach a new unique number in column A.

I have attached a sample of what I would like to see.
I have 13000 of these so if I can I would like to have it automated somehow. I am at a loss for where to start.

Any ideas?

 A           B                C
10001	11390	6/17/2005 11:22	1	X
	13783	9/8/2005 14:38	1	
	18614	2/23/2006 16:20	1	X
	20767	6/21/2006 15:02	1	
10003	10150	3/9/2005 11:00	1	X
	10535	4/29/2005 14:00	1	
	10565	5/2/2005 23:49	1	X
	10966	5/31/2005 12:22	1	
	10985	6/1/2005 8:36	1	X
	11878	7/6/2005 8:11	1	
	12101	7/13/2005 17:54	1	
	14764	10/10/2005 12:22	1	
	17459	1/11/2006 16:07	1	
10004	10032	1/31/2005 14:57	1	X
	10043	2/4/2005 23:26	1	
	10049	2/7/2005 9:00	1	X
	10099	2/22/2005 11:45	1	
	10343	4/11/2005 10:15	1	X
	10581	5/3/2005 10:14	1	
	10585	5/3/2005 11:49	1	
	10595	5/4/2005 7:15	1	
	11061	6/4/2005 9:26	1	
	11217	6/10/2005 16:34	1	X
	11546	6/23/2005 10:40	1	
	11570	6/24/2005 5:12	1	
	12058	7/12/2005 13:45	1	
	12170	7/15/2005 12:42	1	X
	12747	8/3/2005 16:58	1	
	12869	8/8/2005 19:27	1	
	13534	8/30/2005 16:28	1	
	15196	10/25/2005 14:33	1	
	15817	11/16/2005 11:30	1	X
	19855	5/1/2006 12:19	1	
	20898	6/28/2006 18:23	1	

1/19/2007 2:03:52 PM

[Edited on January 19, 2007 at 2:30 PM. Reason : totally misunderstood the problem]

1/19/2007 2:28:58 PM

I started to code something up, but your problem is a bit annoying.

You gotta loop through all of column A looking for new entries. When finding them, you then have to loop through adding X's in C, making sure you don't hit new entires in column A while doing it.

Brute force it would be

For currRow = 1 to 13000
if new entry
make an X
if currRow + 1 and currRow + 2 <> newEntry
make an X
currRow = currRow + 2
if currRow + 1 , +2, +3, +4, +5 <> newEntry
make an X
else, go back to your global while loop
and then do some sort of while loop around that last block of code, popping out if you find an entry in there

i dont have time to code it up for you

1/19/2007 2:41:04 PM

take a look at this - i made four columns, each with a formula for each part of the problem. this will keep it easier to modify, and you can always hide the columns you don't want seen. it should work for you, if not let me know

http://ken.kaisia.com/tww/solution.xls

1/19/2007 2:45:39 PM

^^ too complicated.
As usual, you need to just break it down and use multiple steps, then you can do it all using excel formulas and no VBA.

Use a 2 column solution, where column D is an intermediate answer (which you can hide from view, if you want - or can even put into another column or worksheet or whatever). Column D will hold the index of the current unique value that you're on. so in your case, it would look like

1
2
3
4
1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

=IF(ISBLANK(A3),D2+1,1)

=IF(OR(D2=1,D2=3,MOD(D2,5)=0),"X","")

1/19/2007 2:46:41 PM

here you go

download asap utilities from http://www.asap-utilities.com. that's the best free collection of excel tools in the world. i've been using it for years. install it and make it a default add in (it'll ask you that during installation). it adds a menu at the top.

now highlight a blank column (I'm going to use column H as an example) and go to ASAP Utilities -> Fill -> Quick numbering of cells. Choose the option for change at every change in column A. (It'll make sense when you see it). This will number the column you highlighted, restarting at 1 every time A has a new number.

then in another colum, row 1, do the formula =IF(H1="1","X",IF(H1=3,"X",(IF(MOD(H1,5)=0,"X","")))) and fill all the way down. That'll get you what you want. Best bet is to copy and paste special -> values to take the Xs out of a formula.

HTH

[Edited on January 19, 2007 at 2:48 PM. Reason : no offense]

1/19/2007 2:47:35 PM

heh, that's the same solution as agentlion, just without the first formula. that's a good one. but i still suggest downloading ASAP. it has all the features that excel should really have built in.

1/19/2007 2:49:41 PM

^ yeah, same thing.
be sure to make use of the OR() statement instead of nested IF()s though, when possible. Makes for much more readable formulas.

1/19/2007 2:51:02 PM

Definitely looks like yours will work joe.

Only problem I see though (and hopefully its just a typo) is if you look at the example data he posted, for the repeating 5th, the first "repeat" is on the 6th, then the next repeat is on the 5th, then the next repeat is on the 6th.

Probably an error in how he enterred it, but I think it definitely need to be clarified if every 5th is indeed every 5th, or if it is every 5th+1

1/19/2007 3:14:29 PM

Thanks agentlion!

Worked perfectly!

1/19/2007 3:20:14 PM