M Brian

Excel wizard required!

Can anyone help - I have a big list of numbers (9000 numbers) they all have different attributes in about a few columns.
and I need to come up with a formula - that will count the number of fields that are the same in a row. so that I can look for the biggest run of numbers in a sequence.

i.e.

group no. Free?
1100 N
1101 Y
1102 Y
1103 N
1104 Y

The information I want is that there are 2 'Y's in a row.....
If I just sort by Ys for example it obviously takes the numbers out of sequence....
I was thinking.... of a formula =COUNTIF(IF cell =Y: B5, "=Y")

obviously the if cell bit I want to equal Y is to give the range? is doing my head in anyone know?

anyone help?

Iain Mac

I'm sure there is a way to do this but I have no idea how.

If you need to do it quick and no one else can tell us how, what about doing a DATA, SORT, by Y, THEN by number in the same search?

It should put them into numerical sequence and make it comparatively easy to manually count the sequential ones.

scruffythefirst

One way would be to double your columns with attributes and put a =IF(B2=y,1,) in each one where B2 corresponds to the "free" column then just add sum each of the new cells.

Can you post a bigger bit of the sheet (width wise)

Ok, just read it properly and thats not what your after

hang on

scruffythefirst

Ok, not sure exactly what you are trying to achieve - can you post more of the sheet and fill in by hand what you want.

M Brian

hmmmm bit hard to describe...

but let me try to clarify....

I have a list of around 9000 numbers - the numbers represent groups of part numbers for ordering parts...

out of this list only around 2000 numbers are in use.... but they are randomly used...

i.e. 8001 may be in use... 8003 8051 7999 etc may be free to use.

out of this list I need to find a reasonable amount of 'free' numbers. but they need to be in sequence...

i.e. I need to say... groups 2010-3050 or 1500-1600 etc are free to use.
(but if any number between those two limits is used then I cannot use those batches)

it will take me ages to manually go through 9000 numbers looking for the greatest uninterrupted sequence... so I wanted to do it with a formula?? im close but not there yet!! lol

you can add whatever columns or do whatever with the data - im only using excel to try to save doing it manually.

any clearer?

scruffythefirst

Thats easy, sort the whole lot by the second column then by the first column. That should give you all the free numbers at the top.

Go to the data menu then sort. Select column b as the first option then column A as the second. You should then have all the Y's at the top and you can pick the large quantities of sequential numbers out.

M Brian

thats the problem though.....

i've done that and you can't pick the large numbers out....

as for example.... It might indicate numbers 7123-8998 free.....

but 8125 might be in use.... so therefore I havent got a full sequence of numbers but I wouldnt immediately know because that one would be missing from my list!!

surely there's gotta be a way of getting a formula to count from one letter Y to the next letter Y, stopping if it doesnt equal 'Y' aaaarrrgh lol

scruffythefirst

Ok, sort them as i suggested then in another column put this formula

=IF((a1+1)=a2,,"X")

every time the run of numbers breaks you will get an X in the column. Then if you want to find a large row of numbers you can just look down untill you hit an X. If that isn't automated enough you could put a lookup table or simmilar in to collect each number that corresponds to an X.

scruffythefirst

Or you can just look at the N section of the sort and all the numbers between each N will be free.

1000 N
1001 N
1344 N
1345 N

So 1002 to 1343 Inclusive must be a Y So just put a formula in another column that takes the number above away from its row which will give you the number of free numbers between them - then just scroll down the list and pick one big enough for your purposes.

Ratty

iTrader: (1)

Insert a new row at top of work book, then highlight the new row and Data Filter Autofilter

You can then use the drop downs arrows to filter the data in the workbook based on the entered fields

Simple then to compared the views

SupaMiniCupa

Have you solved this yet?

You have e-mail

M Brian

seen the email!!!

seems champion so far!!


ill let you know how I get on with it when im at work tomorrow!!! cheers all so far!

M Brian

The dog's bollox matey!!!!

got me sorted there!!


think im sorted with the formula aswell!! nice thinking!!


Can't thank you enough there mate (and everyone else how has advised)

SupaMiniCupa

Glad to be of service!!

Now - anyone want to employ me? I do this at my current place and I need a new challenge

scruffythefirst

Fancy sharing the solution then?

Iain Mac

Scruffyfirst - hope it was your idea he used because you think the same way as me with a nice simple DATE SORT, but you then came up with the brilliant formula for each row +1 which will certainly do the job - I've never understood all these filters and functions and things!!!!

SupaMiniCupa

OK, I'm not very good at putting spreadsheets into words, but here goes:

D1= The number of consecutive free codes you need (entered by user)

D5 and downwards = 'XXX' to show which codes are being taken. Blank = free.

C5 and downwards = consecutively numbered product codes.

A5 and downwards = IF(D5<>"",0,A4+1).

D2 = First free product number available =(VLOOKUP($D$1,A:C,3,FALSE)-D1+1)

Its not pretty, but it seems to work