Page 42 of 42 FirstFirst ... 32404142
Results 616 to 628 of 628

Thread: My Weekly Tech Journal

  1. #616
    SB Legend Lieutenant General hotspicyhot's Avatar
    Join Date
    Sep 2007
    Location
    MI6
    Posts
    46,127
    Rep Power
    100

    Default

    Quote Originally Posted by rv102938 View Post
    the one above. you have a solution, but is that exactly what you wanted to do?
    YES... step by step I did... But if you any simpler solution... plz provide

    Also if you know to write macro or something to automate these process... plz give the instructions
    YOUR ONLY LIMIT IS YOU

  2. #617
    SB Guru Major rv102938's Avatar
    Join Date
    Apr 2014
    Location
    Canada
    Posts
    5,150
    Rep Power
    39

    Default

    Quote Originally Posted by hotspicyhot View Post
    YES... step by step I did... But if you any simpler solution... plz provide

    Also if you know to write macro or something to automate these process... plz give the instructions
    why dont you use pivot tables? it should give you what you are looking for. if its not a confidential data, you can share the file with me and i will see what i can do.
    Ek din sher banke jeene se behtar hai do din sher banke jiyo...

  3. #618
    SB Guru Major rv102938's Avatar
    Join Date
    Apr 2014
    Location
    Canada
    Posts
    5,150
    Rep Power
    39

    Default

    i have excel 2013. but except look and feel, it should be similar in previous versions.

    go to Insert --> Pivot tables --> once the table loads, you have to select rows, columns, and values.

    if you select any cell in the pivot table, there will be pivot options loaded on right side. there you will see rows, cols, values i have used for this sheet.

    row is "patta no-name" (this is one item which is combo of no and name)

    values used for this pivot are area, tax, etc.

    there are many options for values - like sum, avg, count. we used sum. i have one special field which is roundup.

    just select any cell in the pivot table and try to play around with the pivot fields on the right, you should understand.

    Pivot table is a great tool for reporting especially where Excel functions become tedious.
    Ek din sher banke jeene se behtar hai do din sher banke jiyo...

  4. #619
    SB Legend Lieutenant General hotspicyhot's Avatar
    Join Date
    Sep 2007
    Location
    MI6
    Posts
    46,127
    Rep Power
    100

    Default

    Quote Originally Posted by rv102938 View Post
    i have excel 2013. but except look and feel, it should be similar in previous versions.

    go to Insert --> Pivot tables --> once the table loads, you have to select rows, columns, and values.

    if you select any cell in the pivot table, there will be pivot options loaded on right side. there you will see rows, cols, values i have used for this sheet.

    row is "patta no-name" (this is one item which is combo of no and name)

    values used for this pivot are area, tax, etc.

    there are many options for values - like sum, avg, count. we used sum. i have one special field which is roundup.

    just select any cell in the pivot table and try to play around with the pivot fields on the right, you should understand.

    Pivot table is a great tool for reporting especially where Excel functions become tedious.
    Can you temme which formula used in this column ? (Q WET Area)
    Last edited by hotspicyhot; 12-02-2017 at 07:34 PM.
    YOUR ONLY LIMIT IS YOU

  5. #620
    SB Guru Major rv102938's Avatar
    Join Date
    Apr 2014
    Location
    Canada
    Posts
    5,150
    Rep Power
    39

    Default

    Quote Originally Posted by hotspicyhot View Post
    Can you temme which formula used in this column ? (Q WET Area)

    <screenshot>

    in O, I have this
    =IF(C160="நஞ்சை","Wet",IF(C160="புஞ்சை","Dry",""))

    then in Q, I use this -
    IF(O160="Wet",K160,0)

    I could directly use this in Q
    if ( c160 = "நஞ்சை" , ...)

    but first I tried to convert Tamil to English for clarification.
    Last edited by rv102938; 13-02-2017 at 09:51 PM.
    Ek din sher banke jeene se behtar hai do din sher banke jiyo...

  6. #621
    SB Guru Major rv102938's Avatar
    Join Date
    Apr 2014
    Location
    Canada
    Posts
    5,150
    Rep Power
    39

    Default

    for pivot, i wouldnt have so much time to create a help doc with screenshot. i suggest you do basic 30 min - 1 hour reading on pivot through Internet how-to stuff, then try to repeat what i did. with this, you should equipped to do most of your work.

    after that if you specific questions, i can help answer. that will be better use of your and my time.
    Ek din sher banke jeene se behtar hai do din sher banke jiyo...

  7. #622
    SB Guru Major rv102938's Avatar
    Join Date
    Apr 2014
    Location
    Canada
    Posts
    5,150
    Rep Power
    39

    Default

    we can put @ Row2 as IF(O2="Wet",2,0) and filled down na ? or what common formula ?

    Why "2"? in column Q, you basically want the area to be displayed in Q only if it is wet, else 0. for dry, you get 0, for wet, you get whatever is in E_Ext.


    for subtotals, i dont know. basically, i dont use pivots and reports much for my work, so i dont have much idea. but its a very powerful tool. you can pretty much do what you want.
    Ek din sher banke jeene se behtar hai do din sher banke jiyo...

  8. #623
    SB Legend Lieutenant General hotspicyhot's Avatar
    Join Date
    Sep 2007
    Location
    MI6
    Posts
    46,127
    Rep Power
    100

    Default Progressive Subtotal

    Progressive (Cumulative) Subtotal in Each Page Break !


    This made me many sleepless nights... But after researching so much excel forums... and VBA codings....

    I found this way... May be some other easier way... But this is easier than manually doing.

    OK Lets get to the things...

    After doing above things ... We will get XL sheet like below...



    We need progressive subtotal (not only that page only) at each page break

    So first go to Page Break Preview


    You see the 33th row is last row of that page (If A4 size)



    So we need to insert a blank row for every 30th row (-2 rows of header and -1 blank row)

    by below Macro

    Code:
    Sub Insert_Row()
    Dim my As Integer, ur As Integer
    On Error GoTo Getout
    ur = InputBox("Enter How Many Row Want to Insert")
    my = InputBox("Enter How Many Rows Want to Skip")
    Application.ScreenUpdating = False
    If my = 0 Or ur = 0 Then Exit Sub
    On Error GoTo Getout
    Range("A" & 3 + my).Select
    Do While ActiveCell.Value <> ""
    5 Range(ActiveCell, ActiveCell.Offset(ur - 1, 0)).EntireRow.Insert
    ActiveCell.Offset(1 + my + ur - 1, 0).Select
    Loop
    Getout:
    Application.ScreenUpdating = True
    End Sub
    To Insert the above Macro

    Press ALT + F11


    and Right Click Sheet1 > Insert > Module > And Paste above Code


    Now Back to XL Sheet

    Run the Macro by ALT + F8

    Run : Insert_Row


    Enter How Many Row Want to Insert : 1


    Enter How Many Rows Want to Skip : 30


    Yurekaaaaaaaa ! You got blank Row before every Page Break !


    Now on First Blank Row

    Put the below formula in C33

    Code:
    =SUBTOTAL(9,C$3:C32)
    And Copy Across the Row till I Row

    Now Select Entire Row and Copy

    Then copy the row to your inserted rows all the way down By doing

    Select Every Blank Row Before @ Every Page Break (By Pressing CTRL together)

    and Paste as Formula

    Eurekkkkkkkaaaaaaaa !

    Now you got Progressive Subtotal at Each Page :

    Now you format it as BOLD

    and Print

    I can sleep now

    HsH
    YOUR ONLY LIMIT IS YOU

  9. #624
    SB Guru Major rv102938's Avatar
    Join Date
    Apr 2014
    Location
    Canada
    Posts
    5,150
    Rep Power
    39

    Default

    cool stuff !!!!!!!!
    Ek din sher banke jeene se behtar hai do din sher banke jiyo...

  10. #625
    SB Legend Lieutenant General hotspicyhot's Avatar
    Join Date
    Sep 2007
    Location
    MI6
    Posts
    46,127
    Rep Power
    100

    Default

    HsH Journal : February 14th, 2017. 10:00 PM

    Learn Simple EXCEL Tricks with ME! [Part - IX]


    A Complete Fast Track Guide To Calculate 10 PART 2 From A REGISTER

    A Step By Step Layman Guide

    Once you get the AREG (by > View Extracts in Tamil Nilam) Just Select ALL and Copy and Paste Special it in Excel Sheet as Unicode (For Tamil Characters)


    Select the all columns and Sort with A Column (survey number) in Sorting

    You will get unnecessary page titles and page subtotals at end of sheet... Select the first unnecessary row and by pressing SHIFT click last row ... and delete those rows.

    Now we got the needed data only like below


    The ways already said in this thread is little bit harder than our beloved RV (He is so much Ravi ) suggested PIVOT Table method

    So lets get down to business

    After you do the required thing to AREA column for Hectare format (0.00.0) HA


    in L column put below formula and copy down (to get the WET AREA)

    Code:
    =IF(C2="நஞ்சை",I2,0)

    and in M column put below formula and copy down (to get WET TAX i.e, WET AREA (in HA) x 12 rupees fixed by TN Govt)

    Code:
    =L2*12

    like above do for DRY


    in N column put below formula and copy down (to get the DRY AREA)

    Code:
    =IF(C2="புஞ்சை",I2,0)

    and in O column put below formula and copy down (to get DRY TAX i.e, DRY AREA (in HA) x 5 rupees fixed by TN Govt)

    Code:
    =N2*5
    To Get Total Area & Total Tax

    In P column & Q Column


    Code:
    =L2+N2
    Code:
    =M2+O2
    put above formula and copy down respectively


    To Round Up the Tax to Nearest Rupees Value (Since Paisas are not collect able due to no longer value of under 1 rupees coins)

    In R Column put below formula and copy down

    Code:
    =ROUNDUP(Q2,0)
    Now you did all the important things to for TAXATION

    But how to Assess the Total Tax for Particular Pattadar (Owner) since one can own many Survey Numbers and A LIST is based on Survey Numbers

    Here Comes the Trick suggested by RV. The God Help PIVOT Tables.

    Now go to Insert --> Pivot tables --> once the table loads, you have to select rows, columns, and values.

    Select Range of Sheet


    In choose fields to add to report

    Select required fields


    In Row Labels

    Select Patta Number and Move all other Fields to Values


    and in Value Field Settings ... Select Sum


    And Number Format (Desired Format) 0.00.0 for AREA and 0.00 for TAX


    Now Copy the Table to other sheet as Paste Special (Values and Formats)

    As you see the Column A contains Both Patta Number and Name. So we need to split the Column into two as Number and Name. So that

    Add new blank column after Column A and Select Column A

    Go To > Data > Texts to Column Select the Delimiters as - (Hyphen)


    Click Finish


    And change the A column to number

    And Now again Select all Columns and Sort By Column A as Ascending...

    Eurekkkkkkkkaaaaaaaaaaa... You got the 10PART 2 LIST

    And Run Below Macro to put Progressive Subtotal at Each Page Automatically :


    Code:
    Sub insertST()
    'Insert Subtotals and add page breaks
        Application.ScreenUpdating = False
        
        Const pBrk = 30 'if A4 sheet and Landscape Mode change number according to your need
        
        ActiveSheet.ResetAllPageBreaks
        ActiveSheet.PageSetup.PrintArea = ""
        LR = Cells(Rows.Count, "A").End(xlUp).Row
        ins = Round(LR / pBrk, 0)
        LR = LR + ins + 1
        For i = 2 To LR
            i = i + pBrk
            Rows(i).Insert Shift:=xlShiftDown
            Cells(i, "C").Select
            ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R2C:R[-1]C)"
            Selection.Font.Bold = True
            Cells(i, "D").Select
            ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R2C:R[-1]C)"
            Selection.Font.Bold = True
            
            Selection.AutoFill Destination:=Range("D" & i & ":I" & i), Type:=xlFillDefault
            ActiveSheet.HPageBreaks.Add Before:=Rows(i + 1)
             
        Next
        Application.ScreenUpdating = True
    End Sub

    Now In header add District | Taluk | Village names

    and Footer (right) add page of pages

    Add background TN symbol (state emblem | temple tower) as PNG File

    and Save & PRINT


    Thatz ALL Folks

    Shouts to RV and David (For Pivot and Macro)

    HsH
    YOUR ONLY LIMIT IS YOU

  11. #626
    SB Guru Major rv102938's Avatar
    Join Date
    Apr 2014
    Location
    Canada
    Posts
    5,150
    Rep Power
    39

    Default

    Quote Originally Posted by hotspicyhot View Post

    Thatz ALL Folks

    Shouts to RV and David (For Pivot and Macro)

    HsH
    Ek din sher banke jeene se behtar hai do din sher banke jiyo...

  12. #627
    SB Legend Lieutenant General hotspicyhot's Avatar
    Join Date
    Sep 2007
    Location
    MI6
    Posts
    46,127
    Rep Power
    100

    Default

    Quote Originally Posted by rv102938 View Post
    YOUR ONLY LIMIT IS YOU

  13. #628
    SB MahaGuru Colonel
    Join Date
    May 2007
    Location
    in.center.of.TALIBAN
    Posts
    17,277
    Rep Power
    88

    Default

    long list ..................

Similar Threads

  1. Laughter Is The Best Medicine
    By hotspicyhot in forum General Discussion
    Replies: 391
    Last Post: 18-11-2014, 02:22 PM
  2. Why call centre guys are paid too much??
    By theachiever in forum Chit Chat Corner
    Replies: 5
    Last Post: 25-06-2010, 03:58 PM
  3. Computer TecH SuPPort Calls [ must read ]
    By Alive in forum Mobile and Gadgets
    Replies: 4
    Last Post: 22-03-2009, 03:48 PM
  4. Computer Tech Support Calls
    By kollam003@gmail.com in forum General Discussion
    Replies: 9
    Last Post: 21-11-2007, 11:11 PM
  5. "Tell them you're too damn stupid to own a computer."
    By Samia in forum General Discussion
    Replies: 15
    Last Post: 23-11-2006, 02:11 PM

Bookmarks

Posting Permissions

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