Results 1 to 11 of 11

Thread: excel

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    tufo's Avatar
    Join Date: May:2011
    Location: Sofia
    Posts: 64

    excel

    , .

    : , , B: 5, 4, 3 .
    , ( 1, 2, 3, 4, 5 5) , B, 1.
    - 4 3 .
    12 .

    .
    , , pivot table ..

    .
    GA P35-DS3 | Intel Pentium E6500 | Adata Extreme DDR2 2x2gb 4-4-4-12 / 2x1gb Corsair 4-4-4-12 | HITACHI 1TB Seagate 1TB| ATI Radeon HD 4670 | TEGAN 430

  2. #2
    Hardcore atheist rado84's Avatar
    Join Date: Jan:2007
    Location:
    Posts: 938
    , ...
    1 , , 2, , . , , .
    - 1, , "", , "". , .

    :
    https://www.mediafire.com/?tsswlp07nn8hlej
    AsRock B85M Pro4, Core i7-4770, Palit GTX 1660 Ti 6GB, Corsair CX750 PSU, Corsair 32GB DDR3-1600, Storages 6.1TB, Arch Cinnamon

  3. #3
    tufo's Avatar
    Join Date: May:2011
    Location: Sofia
    Posts: 64
    , . 1200 2 20 enter , . ....
    , , pivot table ..
    GA P35-DS3 | Intel Pentium E6500 | Adata Extreme DDR2 2x2gb 4-4-4-12 / 2x1gb Corsair 4-4-4-12 | HITACHI 1TB Seagate 1TB| ATI Radeon HD 4670 | TEGAN 430

  4. #4
    Web Crocodile
    Join Date: Sep:2003
    Location:
    Posts: 323


    Option Explicit
    Sub ExpandData()
    Dim a As Variant, b As Variant
    Dim n As Long, r As Long, rr As Long, nr As Long
    a = Range("A1").CurrentRegion
    n = Application.Sum(Columns(4))
    Redim b(1 To n, 1 To UBound(a, 2))
    For r = LBound(a, 1) To UBound(a, 1)
    For rr = 1 To a(r, 4)
    nr = nr + 1
    b(nr, 1) = a(r, 1)
    b(nr, 2) = a(r, 2)
    b(nr, 3) = a(r, 3)
    b(nr, 4) = a(r, 4)
    Next rr
    Next r
    Range("A1").Resize(UBound(b, 1), UBound(b, 2)) = b
    End Sub

  5. #5
    Registered User
    Join Date: Aug:2009
    Location:
    Posts: 265
    :
    1. , :
    1: 1
    2: =1+C1
    A3: =A2+C2
    ...
    :
    1: =VLOOKUP(ROW();Sheet1!$A$1:$C$3;2)
    2: =VLOOKUP(ROW();Sheet1!$A$1:$C$3;2)
    ...

    2. , :
    1: =INDEX(Sheet1!$A$1:$C$3;MATCH(ROW();INDE X(Sheet1!$A$1:$C$3;;3);1);1)
    ...

    3. :
    1:
    A2: =IF(COUNTIF(A$1:A1;A1)<VLOOKUP(A1;Sheet1 !$A$1:$B$3;2;0);A1;INDEX(Sheet1!$A$1:$B$ 3;MATCH(A1;INDEX(Sheet1!$A$1:$B$3;;1);0) +1;1))

    . - ( ).
    . 1, - "row()" .
    Last edited by Avis; 19th February 2014 at 10:30. Reason:

  6. #6
    Hardcore atheist rado84's Avatar
    Join Date: Jan:2007
    Location:
    Posts: 938
    Quote Originally Posted by tufo View Post
    , . 1200 2 20 enter , . ....
    , , pivot table ..
    - . , , .
    , , , . , , 1200 - , .

    https://www.mediafire.com/?yokcpngmfejuoau
    AsRock B85M Pro4, Core i7-4770, Palit GTX 1660 Ti 6GB, Corsair CX750 PSU, Corsair 32GB DDR3-1600, Storages 6.1TB, Arch Cinnamon

  7. #7
    Web Crocodile
    Join Date: Sep:2003
    Location:
    Posts: 323
    Quote Originally Posted by rado84 View Post
    - . , , .
    , , , . , , 1200 - , .

    https://www.mediafire.com/?yokcpngmfejuoau
    , . , 1200 ?

  8. #8
    Registered User hateras's Avatar
    Join Date: Jan:2011
    Location: Kronos III
    Posts: 1,028
    VBA , :
    Code:
    Const FruitNameCol = 1  'column "A"
    Const FruitCountCol = 2 'column "B"
    
    Sub FruitsDistribution()
    Dim WB As Workbook
    Dim SrcSheet As Worksheet
    Dim DestSheet As Worksheet
    
    Set WB = Application.ActiveWorkbook
    Set SrcSheet = WB.Sheets(1) 'workbook's first sheet ("Sheet1")
    Set DestSheet = WB.Sheets(2) 'workbook's second sheet ("Sheet2")
    
    Dim FruitName As String 'fruit label
    Dim FruitCount As Integer 'fruit count
    Dim SrcRow As Integer 'current source row
    Dim DestRow As Integer 'current destination row
    
    SrcRow = 1 'start row on source sheet
    DestRow = 1 'start row on destination sheet
    FruitName = Trim(SrcSheet.Cells(SrcRow, FruitNameCol))
    'read source's column A until first emty cell
    While FruitName <> ""
        FruitCount = CInt(Trim(SrcSheet.Cells(SrcRow, FruitCountCol)))
        'fill destination
        For i = 1 To FruitCount
            DestSheet.Cells(DestRow, FruitNameCol) = FruitName
            DestSheet.Cells(DestRow, FruitCountCol) = 1
            DestRow = DestRow + 1
        Next
        SrcRow = SrcRow + 1
        FruitName = SrcSheet.Cells(SrcRow, FruitNameCol)
    Wend
    End Sub
    Sheet1 / sheet workbook/ Sheet2 / sheet workbook/
    ""
    "" "B"
    ASRock B550M Pro 4; Ryzen R5 3600; 2x16 GiB G.SKILL Aegis 3200; 1TB Samsung QVO 960 + 3TB Seagate IronWolf; Zalman Z1

  9. #9
    Hardcore atheist rado84's Avatar
    Join Date: Jan:2007
    Location:
    Posts: 938
    Quote Originally Posted by INJ View Post
    , . , 1200 ?
    :
    Quote Originally Posted by tufo View Post
    1200
    Quote Originally Posted by tufo View Post
    AsRock B85M Pro4, Core i7-4770, Palit GTX 1660 Ti 6GB, Corsair CX750 PSU, Corsair 32GB DDR3-1600, Storages 6.1TB, Arch Cinnamon

Posting Permissions

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

Copyright © 1999-2011 . .
iskamPC.com | mobility.BG | Bloody's Techblog | | 3D Vision Blog |