Excel Questions

Started by magickingdom, February 21, 2008, 12:29:11 PM

Previous topic - Next topic

Maiden1

If you don't mind having extra columns you can highlight all the cells that you want to format then use

Data>Text to Columns>

Then have the delimiter as    :

Will create 4 columns as below, highlight all these rows and you can do find and replace for all the letters then.

d24   h21   m44   s27.
d17   h02   m59   s50.
d10   h04   m51   s19.
d08   h04   m28   s51.
d08   h04   m03   s06.
d07   h04   m47   s07.
d07   h04   m45   s04.
d06   h12   m56   s43.
d03   h05   m06   s02.
d02   h20   m57   s23.
d02   h19   m57   s08.
d02   h14   m19   s03.

You could always just do mid like above though ::)
There are no proofs, only opinions.

Atticus_Finch

Thanks a lot gents,  appreciate your help.
"The one thing that doesn't abide by majority rule is a person's conscience."

mick999

Hi there,


A question on Vlookups


I am just doing a VLOOKUP on a particular file as below ..

=VLOOKUP(B18,'[Costs_2011_12_11.XLSX]CDB_Output'!$A$2:$D$65536,4,FALSE)


My Costs file has 80,000 rows and if I go above 65536, I get an invalid reference, that I cannot reference Rows beyond 65536 ..
Is there any way around this ??

Cheers ..

armaghniac

#228
Recent versions of Excel can have more than 65536 rows. However, I would not do big operations like this in Excel. At least use MSAccess.
If at first you don't succeed, then goto Plan B

mick999

Quote from: Mac Eoghain on December 12, 2011, 04:12:43 PM
Mick999 - have you tried splitting the costs file into 2 files - then do the vlookup across the 2 workbooks?

Hi Mac

I'll try that ..

just wondered was there some other way .. of seperating the vlookup into a before and after 65536 type scenario, but I've been trying and it doesn't seem to work either ..

I'm using Excel 2007, Does anyone know which version works with this No, Of rows ?

Thanks,

Mick

glens73

Quote from: mick999 on December 12, 2011, 09:16:59 PM
Quote from: Mac Eoghain on December 12, 2011, 04:12:43 PM
Mick999 - have you tried splitting the costs file into 2 files - then do the vlookup across the 2 workbooks?

Hi Mac

I'll try that ..

just wondered was there some other way .. of seperating the vlookup into a before and after 65536 type scenario, but I've been trying and it doesn't seem to work either ..

I'm using Excel 2007, Does anyone know which version works with this No, Of rows ?

Thanks,

Mick

Are you doing the lookup in a separate file?

If so, save that file as 2007 and the lookup should work. Do the lookup between columns a:d i.e. remove the row numbers

Croí na hÉireann

I have numerous excel files with a column of data in each. Each column has over 10,000 entries in it. These entries correspond to how active or not the case subject was every minute (it's a study of the benefits of exercise in cancer research). The entries range from 0 up to 2000 for every minute of every day for a week. I need to extract how many times each subject undergone vigorous activity for a period of more than 10 minutes everyday. Vigorous activity equals a number greater than 984. i.e. I need to extract how many consecutive entries of 10 or more are greater than 984. In the below example the output would be 15.

352
277
921
74
1988
2153
3027
2364
2568
3923
3745
3122
3225
3529
3175
2941
3721
3298
3678
603
63
560
507
134
0
167

Now to make a difficult assignment even more tedious there is one exception I need to look out for. These consecutive entries of 10 or more greater than 984 can be separated by a gap of one entry (less than 984) in every 5 entries, i.e. at least five consecutive entries greater than 984 can be followed by one entry less than 984 where it is in turn followed by at least five consecutive entries greater than 984. In the below example the output would be 13.

277
921
74
1988
2153
3027
2364
2568
603
3678
3475
3204
2962
3982
2653
2236
1425
507
134
0
167

I hope that is somewhat clear and the gaaboard gods are smiling. It takes me about 15 minutes to search through each file and I've worked out it will take me 75 hours to sort through all the files I have. HELP!!!  :'(
Westmeath - Home of the Christy Ring Cup...

sheamy

#232
define 'numerous' amount of files...10, 100, 1000?

also, are they csv or xls?

Your first job is to get them all on the same sheet. Should be simple enough after that. A VBA macro will probably work to achieve first part. A quick google or two will throw you up one of those. Then write a formula to calculate what you want for one column and expand that out to all.

Harold Disgracey


Hardy

#234
This should work, but test it – there could be a combination of data I haven't thought of.

Put your data in column A, starting at, say, Row 10.
In cell B10: =IF(A10>984,B9+1,0)  - counts consecutive entries >984.
In cell C10: =IF(B10>=5,IF(B11=0,IF(B12>0,"x",""),""),"") – marks a gap after five consecutive entries >984.
In cell D10: =IF(B10>9,IF(B11=0,1,0),0) – counts instances of your first criterion.
In cell E10: =IF(C4="x",IF(B10>=5,1,0),0) – counts instances of your second criterion.
Of course, copy A10:E10 all the way down to the end of your data.
In some cell, put =SUM(D10:E65536) – the total number of occurrences.


(I'm assuming that ten OR MORE consecutive entries >984 counts as one occurrence - i.e. 19 consecutive >984 is still just one occurrence of your criterion 1 and likewise for criterion 2:  5+5 and 5+13 are each one occurrence.)


[Edit - sorry - you need to start the data column further down than Row 2, as I originally had it, to allow for checking back six rows. I've made the changes above]

Maiden1

I was thinking something like above  ::) or you could write a macro something like below to do a max count if all the values where always in column A.

Private Sub cmdCount_Click()

Dim intCurrentCount As Integer
Dim intCurrentRow As Integer
Dim intCountConsecutive As Integer
Dim intMaxCount As Integer
Dim blnFound As Boolean

blnFound = True
intCurrentCount = 0
intCurrentRow = 0
intMaxCount = 0

While blnFound

    intCurrentRow = intCurrentRow + 1
   
    If Cells(intCurrentRow, 1) = "" Then
   
        blnFound = False
   
    Else
   
        If Cells(intCurrentRow, 1) > 984 Then
           
            intCurrentCount = intCurrentCount + 1
            intCountConsecutive = intCountConsecutive + 1
       
        Else
       
            If intCountConsecutive < 5 Then
           
               
                If intMaxCount < intCurrentCount Then
               
                    intMaxCount = intCurrentCount
               
                End If
               
                intCurrentCount = 0
           
            End If
           
       
            intCountConsecutive = 0
           
        End If
   
    End If


Wend


Cells(1, 3) = intMaxCount

End Sub
There are no proofs, only opinions.

Croí na hÉireann

Thanks lads. Was thinking of something like Hardy's but macros might be what I'm really after, excel skills are poor to never used, thanks for the idea and the work Maiden.

Did a bit of coding in college so just stepping through your code am I right in thinking that will just return the maximum number of consecutive rows that are greater than 984?

I need to record every instance where there is at least 10 consecutive entries great than 984 (remember there can be a gap of one entry less that 984 after at least 5 consecutive entries as long as it is followed by another 5 consecutive entries greater than 984). Ideally row C will store every instance of these consectutive entries so, for example, if you take the instance of all the numbers in my previous post the output would be:

15
13

I suppose I can use something like the below to save each consecutive count number once it reaches two entries less than 984?

intSaveRow + 1
Cells(intSaveRow , 3) = intCountConsecutive
Westmeath - Home of the Christy Ring Cup...

Maiden1

#237
Quote from: Croí na hÉireann on February 28, 2012, 04:31:55 PM
Thanks lads. Was thinking of something like Hardy's but macros might be what I'm really after, excel skills are poor to never used, thanks for the idea and the work Maiden.

Did a bit of coding in college so just stepping through your code am I right in thinking that will just return the maximum number of consecutive rows that are greater than 984?

I need to record every instance where there is at least 10 consecutive entries great than 984 (remember there can be a gap of one entry less that 984 after at least 5 consecutive entries as long as it is followed by another 5 consecutive entries greater than 984). Ideally row C will store every instance of these consectutive entries so, for example, if you take the instance of all the numbers in my previous post the output would be:

15
13

I suppose I can use something like the below to save each consecutive count number once it reaches two entries less than 984?

intSaveRow + 1
Cells(intSaveRow , 3) = intCountConsecutive

Yes that is correct it only records the maximum in row 1,3

If you want to record everytime it > 15 then

Where it has

                If intMaxCount < intCurrentCount Then
               
                    intMaxCount = intCurrentCount
               
                End If

Change that to

                If intCurrentCount >= 15 Then
               
                        Cells(intCurrentRow - 2, 3) = intCurrentCount
               
                End If
There are no proofs, only opinions.

Croí na hÉireann

That works maiden, getting my head around it now, thanks again. Where's the applause smile??? This one will have to do  :-*






:P
Westmeath - Home of the Christy Ring Cup...

Maiden1

Quote from: Croí na hÉireann on March 01, 2012, 04:40:51 PM
That works maiden, getting my head around it now, thanks again. Where's the applause smile??? This one will have to do  :-*






:P
haha good stuff :)
There are no proofs, only opinions.