1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to convert year based multi line data in single line for each customer?

Discussion in 'VBA Macros' started by Chirag R Raval, May 23, 2018.

  1. Chirag R Raval

    Chirag R Raval Member

    Messages:
    638
    Dear Sir,

    Attached an excel file with first original data sheet and 2nd sheet for mention requirement.

    Database ,1 party's records spread in multiline lines based on year.

    Require to convert that multiline record for single party in to 1 line (1 row)

    As per screen shot below.

    how to in single line.jpg

    I manually take first step is insert 6 columns after last column(avg)
    & copy heading's 6 cells as below
    copy block of 6 cells.jpg

    replace heading as next years data

    2- REPLACE YEAR WITH NEXT YEAR.jpg

    now just move 6 cells of data under data to newly created 6 headings

    move block of 6 cells.jpg

    I require party's record in single line due to I want to convert it as Access database as party wise single record so I can create party wise report as single block from Access
    and also assign primary key to each unique party wise record .

    This is just 173 records , just 173 parties there...but in excel its data spread by year
    in 1034 lines .

    You can see in my sample requirement sheet, converted 7 rows data for 1 party convert in single line as 52 columns

    hope I described my requirement well .

    Regards,

    Chirag Raval

    Attached Files:

    Last edited: May 23, 2018
  2. vletm

    vletm Excel Ninja

    Messages:
    4,148

    Attached Files:

  3. Asheesh

    Asheesh Excel Ninja

    Messages:
    1,120
    Another solution using formulae.

    Follow the below steps:

    1. Copy data from Column A to Column J of “Original” worksheet and paste it on new worksheet e.g. “Output”

    2. Select Column A:J of “output” tab and remove duplicates.

    3. Use the below formula in K2 of Output tab

      IFERROR(INDEX(ORIGINAL!$D$2:$Q$1034,MATCH(1,(ORIGINAL!$D$2:$D$1034=Output!$D2)*(--LEFT(K$1,4)=ORIGINAL!$K$2:$K$1034),0),MATCH(MID(K$1,FIND("-",K$1)+1,20),ORIGINAL!$D$1:$Q$1,0)),"")


      Drag it down and then right and you will have the results.


      Note: This formula must be acknowledged with CTRL + SHIFT + ENTER

    Attached Files:

  4. Chirag R Raval

    Chirag R Raval Member

    Messages:
    638
    Dear Sir @vletm & @Asheesh

    Sir, @vletm, Just Great, ...With Many Nested loops, thank you very much sir,
    You are make my Day sir,

    Sir, @Asheesh , many Thanks , I will be check that & soon revert back..

    I just try to understand your given magical code.

    I will be back..

    Regards,

    Chirag Raval
  5. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Hi !

    « Nested loops » ?‼

    As a beginner level copy (Destination.Value = Source.Value)
    that just needs a single loop only, instant result via about 15 codelines …
  6. Marc L

    Marc L Excel Ninja

    Messages:
    4,253

    Chirag, I hope you saw the wrong results from post #2
    for destination lines not starting in 2011 …
    Chirag R Raval likes this.
  7. vletm

    vletm Excel Ninja

    Messages:
    4,148
    Chirag R Raval
    There seems all parties have same years data.
    That make some challenges.

    But as Marc L wrote, he'll do this with
    a single loop, instant result via about 15 codelines ...
    then that would be okay soon too.

    Or You can modify that my sample code too.
    Chirag R Raval likes this.
  8. Chirag R Raval

    Chirag R Raval Member

    Messages:
    638
    Dear Sirs @vletm & Sir @aashish

    I want to say There are my mistake, in database ,some party not start from year "2011" in my original database (screen shot attached)

    it is my mistake.jpg

    and result screen shot below.

    loop result.jpg

    should I must maintain constancy in database , in year as 2011 to 2017 for every party?


    Dear Sir @aashish , though sheet name verify as "Output", remove duplicate from ("A :J"), Though In K2 press (ctrl+shift+enter)Formula also not work .

    as per below

    formula not work.jpg

    Please Help.

    Regards,

    Chirag Raval
    Last edited: May 24, 2018
  9. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    No as that just needs a code respecting a child level logic
    and it's not a mistake, just how the database is built …​

    According to your attachment as it's just to copy source rows
    to destination rows, it needs a single loop only.

    For each source row :

    • if BUY1 cell is different from the previous row,
    copy A:J cells to a new destination row.

    • Copy source L:Q cells to destination row according to the year …


    Paste next code to REQUIREMENT worksheet module :​
    Code (vb):
    Sub Demo1()
        Dim L&, R&
        Me.UsedRange.Offset(1).Clear
        Application.ScreenUpdating = False
            L = 1
    With Worksheets("ORIGINAL").UsedRange.Rows
        For R = 2 To .Count
            If .Cells(R, 5).Value <> .Cells(R - 1, 5).Value Then
                L = L + 1
                Cells(L, 1).Resize(, 10).Value = .Item(R).Columns("A:J").Value
            End If
                Cells(L, 11 + (.Cells(R, 11).Value - 2011) * 6).Resize(, 6).Value = .Item(R).Columns("L:Q").Value
        Next
    End With
        Application.ScreenUpdating = True
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
  10. Chirag R Raval

    Chirag R Raval Member

    Messages:
    638
    Dear Sir @Marc L,

    Just Great, Amazing , As per required, thank you very much for give me power to create party wise report which can design in Access & which I need to distribute among them.

    Yes , I realise that it is just copy paste requirement.

    Though , I need little time to fully understand your loop. but I think it is very flexible for my future requirement & modification.

    As per , @Asheesh 's formula way, why I can not apply that though follow all instructions? where I made mistake? hope I can learn also formula way.

    Many Thanks sir @ Marc L again for forever useful code.

    Regards,

    Chirag Raval
  11. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    As the loop is yet explained in my previous post before the code …

    Instead of BUY1 cell you could use FIN_BUY_FOR_NAME cell …
    Chirag R Raval likes this.
  12. Chirag R Raval

    Chirag R Raval Member

    Messages:
    638
    Dear Sir @Marc L ,

    Each buyer's number changes periodically as per their PAN no or ownership , or agreement change , so I must to maintain all buyer No of single party & other data to maintain data of their orders, purchase, dispatch goods as per their orders, etc. (year wise) & hence instead of single number , I have many buyer no for the same party in database.

    Thank you very much for you kind help..& pointing towards flexibility of this code as per future requirement.

    Regards,

    Chirag Raval
  13. Asheesh

    Asheesh Excel Ninja

    Messages:
    1,120
    @Chirag R Raval
    Please apply the formula on the worksheet and share the workbook. I will then go through it and let you know the issue.
  14. Chirag R Raval

    Chirag R Raval Member

    Messages:
    638
    Dear Sir @Asheesh ,

    Thank you for your help
    Here its attached with my try as per your kind guidelines.

    Regards,

    Chirag Raval

    Attached Files:

  15. vletm

    vletm Excel Ninja

    Messages:
    4,148
    Chirag R Raval -
    I want to say There are my mistake, in database ,some party not start from year "2011" in my original database
    should I must maintain constancy in database , in year as 2011 to 2017 for every party?
    It's always good to get samples first.
    But do those solutions notice also
    if there year start from 2009 or 2013,
    if some year totally missed ( eg if there are only years 2011 and 2017)
    or how to handle next year (2018) too?
    Including those 'REQUIREMENT'-sheet headers.
    Test this sample...

    Attached Files:

  16. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Chirag,

    if result worksheet row #1 is not pre-filled,
    I would use an advanced filter to extract unique years
    in order to create all corresponding headers …
    Chirag R Raval likes this.
  17. Chirag R Raval

    Chirag R Raval Member

    Messages:
    638
    Dear Sir @Marc L,

    Thank you very much Sir for your kind effort to resolve this issue & thanks for possible notable points in main database as year factor with cover possible aspects.

    Also Sir @Marc L 's Advance Filter solution may be another good option for this matter. but may be difficult to copy-paste with match, Source data's year with destination heading year & paste there.

    hope there are to learn more about this.

    Regards,

    Chirag Raval
  18. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    No it's quite easy and may be in a procedure with 4 arguments :

    - source data worksheet : "ORIGINAL"

    - break year header as all before this header is fixed and all after this
    header is for columns according to years : "YEAR"

    - break buyer ID header in order to create a new destination row when ID changes : "FIN_BUY_FOR_NAME", "PARTY" or "BUY1" ?

    - Destination worksheet : "REQUIREMENT".

    As any header can be easily found with the child level MATCH
    worksheet function as yet shown in your own threads
    like with the VBA Find method, no matter …

    Using an advanced filter just to extract unique years then sort
    in order to create headers according to years
    but not to copy data as you already have my demonstration for that …

    All this is at beginner level as it's just copying data, nothing special !

    But this kind of procedure is worth only for different reports to create
    with same data structure but if it's only for a single workbook
    the procedure can directly create the report with hard coded
    worksheets and break columns # even if the result headers are not filled …
    Chirag R Raval likes this.
  19. Chirag R Raval

    Chirag R Raval Member

    Messages:
    638
    Dear Sir @Marc L ,

    Thanks.

    your Four Arguments I reads 4 times, but can not set concept in mind.

    " break year header as all before this header is fixed and all after this
    header is for columns according to year : "YEAR" "

    "break buyer ID header in order to create a new destination row when ID changes : "FIN_BUY_FOR_NAME", "PARTY" or "BUY1" ? "

    Year A Buyer ID is key point?, break means transpose years in columns or blank line ?

    can you describe your post no 18 with step no?

    please help.

    Regards,
  20. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    See the loop explanation in post #9 and the code logic …

    First it depends on the destination worksheet headers pre-filled or not,
    if worksheets names are the same or may change,
    if the data structure is always the same
    (ID & YEAR columns at same order position, same number of columns before
    & after YEAR column), so what is fixed and what is not and far above all,
    if it's for an unique workbook or may be reused in others …

    If result headers are not filled, what is the logic to create them ?
    What is the logic to create a new row in result worksheet ?
    That's the easy questions to answer - child level logic - before
    starting to write any code or even creating any forum thread …
  21. Chirag R Raval

    Chirag R Raval Member

    Messages:
    638
    Dear Sir @ Marc L,

    Thank you for you reply & sorry for late reply.

    I can assume that on core /base field (on final buyer no in this case)
    we can advance filter & match of data with year no in columns &
    copy or cut copy six block (fixed six blocks of that row) data on appropriate
    year column header


    answers
    (1) headers not always prefilled
    (but I can prefill manually for code not become complex).

    (2) Sheet Name may be differ (I want to use next sheet of data-any name it
    have) so at the last of sheets count..

    (3)data structure always not same due to new buyer no can be generated in
    future for same buyer.

    (4) this workbook's data use for access for prepare report.so
    sorry I can not understand your 4th point.

    (5) if the headers are not filled , logic for create that 6 block for every
    new year added in database

    (5) logic for create new row/record in result worksheet is
    next unique buyer no encountered in column
    "FIN_BUY_FOR_NAME" columns "c" )

    another point is database starting year is not fixed ,
    may be old data 2007, or previous can be encountered in database.
    that means we must first check by code that when whole data base's
    start year & what is end year. so it can be effect on fixed structure
    of six blocks of columns that how many block require based on year

    hope I can provide all detail which require for code.

    also attached screen shot as visual answer of your questions.

    required structure.png

    hope you help sir,

    Regards,

    Chirag Raval
  22. vletm

    vletm Excel Ninja

    Messages:
    4,148
  23. Chirag R Raval

    Chirag R Raval Member

    Messages:
    638
    Dear Sir @vletm ,

    Thanks for your reply,

    I already many times download your attached file (that already downloaded & successfully run your code-before ) with code from post no 15.

    but now, some mistake in my excel that I can not see your code in it not in any sheet module nor workbook

    I think your code do its job perfectly, but I can not realise & can't deep study that time.

    can you please paste your code here ?


    Regards,
  24. vletm

    vletm Excel Ninja

    Messages:
    4,148
    Chirag R Raval
    If 'my code' is missing then try next:
    1) Move Your mouse on that file's [ Do It ]-button
    2) Press 'Right Click'
    3) Select 'Assign Marco...'
    4) Press [ Edit ]-button
  25. Chirag R Raval

    Chirag R Raval Member

    Messages:
    638
    Dear Sir @vletm,

    I will revert soon.
    Actually I lost my heraricy structure of VBA that have expand and colleps button.

    All modules,forms, class modules ,sheets modules,workbook modules all are mixed up .

    So I try to find your button object code in single ,pereral list.but not found.

    I will revert back.

    Regards,

    Chirag Raval

Share This Page