Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    Thanks
    2
    Thanked 1 Time in 1 Post

    Collect non contiguous data (excel 2002 /2003)

    Hi

    In the attached spreadsheet in columns B,C,I,J,K,L,M data us pulled in from other sheets and goes into a seperate range, but this creates non contiguous rows, ie there is data in rows 30,31 and 245,26. Is there a way I can tranfer this data to Columns O to Z but this be in contiguous rows ie rows 30 to 34

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Collect non contiguous data (excel 2002 /2003)

    I am not sure what you are after exactly.

    If you are creating code, you can select the region (noncontguous) and loop thru the range (each area and each cell in that area) putting the items into the next cell.

    Also if you are coding, you could also, copy each contiguous section separately Copy 30/31 then 245/246

    If you need more details could you be more specific about what you need...

    The cells aslo are linked and am not sure exactly how that enters into it since we don't have access to the source data.

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Collect non contiguous data (excel 2002 /2003)

    If I understand, a scheme with formulas could be:

    In A30 enter: 1
    In A31 enter:
    <pre>=A30+IF(B31=0,0,1)</pre>

    Copy A31 to A32:A439

    Delete A52:A64
    In A65 enter:
    <pre>=A51+IF(B65=0,0,1)</pre>


    Delete A243:A244
    In A245 enter:
    <pre>=A242+IF(B245=0,0,1)</pre>


    These Column A values may be hidden by format- cells-number(tab) -custom (without the quotes) ";;;"

    In n29:N244 fill in the series from 1 to 216. These Column N values may be hidden by format- cells-number(tab) -custom (without the quotes) ";;;"

    In O26 enter2, in P26 enter 3, in V26:Z26 enter 9-13 These row 26 values may be hidden by format- cells-number(tab) -custom (without the quotes) ";;;"

    In O29 enter the formula"
    <pre>=IF($N29>MAX($A$30:$A$439),"",VLOOKUP($N29,$A $30:$M$439,O$26,0))</pre>

    Copy it to P29
    Copy it to V29:Z29
    Copy B30:M30 and paste-special formatting to O29:Z29

    Copy o29:Z29 to O30:Z244

    The scheme works by incrementiing the values in col A if there is something in column B. Then you dod a VLOOKUP with the values in N on these values using the row 26 as the column

    Steve

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Collect non contiguous data (excel 2002 /2003)

    Hi Steve

    You understand correctly what I am trying to do

    I tried to apply the instructions you gave it looks to work OK except I cant get the fourth Item to transfer, Incidently I would like to apply this right down to row 555,

    I have attached the file to show how far I have got, as you will see the fourth Item does not transfer over.

    Thanks Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Collect non contiguous data (excel 2002 /2003)

    I don't think you followed the directions exactly...
    You have (eg) in A246
    <pre>=A2453+IF(B246=0,0,1)</pre>


    It should be:
    <pre>=A245+IF(B246=0,0,1)</pre>


    I think from A246 to the end, you have copied the wrong formula. Copy A246 (after changing it) down the column. It can go to A555 if you want. As I detailed specifically above, you will have to delete the cells between the "regions" (A440:A441, A513:A514, A535:A536) and change the first in each region (A441, A514, A536) to refer to the last in the previous region instead of the cell above (you can just edit <F2> in the cell and drag the blue outline from the cell above to the last cell in the region above and it will change the formula for each one.)

    You will then have to change the lookup range from 439 to 455. Selecting the output region and doing and Edit - replace (Find: $436 Replace with: $455) should do it without too much trouble.

    To get rid of the NAs you need to fill a number in column N or change the intial IF to also put a null if = zero...

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collect non contiguous data (excel 2002 /2003)

    Does this code do what you want? If so, I would suggest calling it from the worksheet activate event routine so that this portion of the worksheet is updated every time the sheet is activated.

    <pre>Public Sub Consolidate()
    Dim I As Long, J As Long
    J = 0
    With Worksheets("Sheet1")
    .Range("O29:Z244").ClearContents
    For I = 28 To .Range("B65536").End(xlUp).Row - 1
    If .Range("B1").Offset(I, 0).Borders(xlEdgeTop).LineStyle <> xlLineStyleNone And _
    .Range("B1").Offset(I, 0).Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone Then
    If .Range("B1").Offset(I, 0).Value <> 0 Then
    .Range("O29").Offset(J, 0).Value = .Range("B1").Offset(I, 0).Value
    .Range("P29").Offset(J, 0).Value = .Range("C1").Offset(I, 0).Value
    .Range("V29").Offset(J, 0).Value = .Range("I1").Offset(I, 0).Value
    .Range("W29").Offset(J, 0).Value = .Range("J1").Offset(I, 0).Value
    .Range("X29").Offset(J, 0).Value = .Range("K1").Offset(I, 0).Value
    .Range("Y29").Offset(J, 0).Value = .Range("L1").Offset(I, 0).Value
    .Range("Z29").Offset(J, 0).Value = .Range("M1").Offset(I, 0).Value
    J = J + 1
    End If
    End If
    Next I
    End With
    End Sub
    </pre>

    Legare Coleman

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Collect non contiguous data (excel 2002 /2003)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>Nice compact code.

    One comment:
    Your code gets a runtime error (type mismatch) on the line:
    <pre>If .Range("B1").Offset(I, 0).Value <> 0 Then</pre>


    Since B29 is "NSI" so can not be compared to zero. SInce Braddy was not interested in transfering this row (based on his initial question) you could change the line from:
    <pre>For I = 28 To .Range("B65536").End(xlUp).Row - 1</pre>


    to
    <pre>For I = 2<font color=red>9</font color=red> To .Range("B65536").End(xlUp).Row - 1</pre>


    and "solve the problem", though if there is a possibility of other "text" in column B the logic will have to be changed to be able to account for text and numbers...

    Steve

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collect non contiguous data (excel 2002 /2003)

    Interesting, it does not get an error on XL2K. I think Steve has XL97, has anyone tried it on any other versions?
    Legare Coleman

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Collect non contiguous data (excel 2002 /2003)

    Your code runs without error on Excel 2002 SP-3.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collect non contiguous data (excel 2002 /2003)

    Must be a 97 problem. Thanks.
    Legare Coleman

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Collect non contiguous data (excel 2002 /2003)

    Hi Legare

    Sorry for the delay in replying.

    I used your code in Excel 2003 and it worked fine with no errors, thanks for that, I also used Steve's version once I had corrected my errors and that worked fine.
    What I liked about Steve's version with comprehensive instructions was I can adapt that for use elsewhere, because of my limited knowledge of VBA I can only use yours in this instance.

    Having said that I am most grateful for both contributions thanks to all

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Collect non contiguous data (excel 2002 /2003)

    Yes. I use XL97.

    IN XL97 VB seems very sensitive of the datatype. In cases like this I would have to check the type before comparing...

    Since it is not a problem for the user or the newer versions, there is no need to worry about it.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •