• 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...

  • 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?

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
 

Attachments

Last edited:

Asheesh

Excel Ninja
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
 

Attachments

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
 

vletm

Excel Ninja
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.
 
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:

Marc L

Excel Ninja
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:
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 !
 
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
 
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
 

vletm

Excel Ninja
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...
 

Attachments

Marc L

Excel Ninja
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 …
 
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
 

Marc L

Excel Ninja
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 …
 
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,
 

Marc L

Excel Ninja
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 …
 
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
 
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,
 

vletm

Excel Ninja
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
 
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
 
Top