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

#### Chirag R Raval

##### Member
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.

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

replace heading as next years data

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

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

• 123.4 KB Views: 8
Last edited:

#### Attachments

• 75.4 KB Views: 8

#### Asheesh

##### Excel Ninja
Another solution using formulae.

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

• 221.7 KB Views: 6

#### Chirag R Raval

##### Member
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

#### Marc L

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

#### Marc L

##### Excel Ninja

Chirag, I hope you saw the wrong results from post #2
for destination lines not starting in 2011 …

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

#### Chirag R Raval

##### Member
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)

and result screen shot below.

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

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 !

#### Chirag R Raval

##### Member
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

#### Marc L

##### Excel Ninja
As the loop is yet explained in my previous post before the code …

#### Chirag R Raval

##### Member
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

#### Asheesh

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

#### Chirag R Raval

##### Member
Dear Sir @Asheesh ,

Here its attached with my try as per your kind guidelines.

Regards,

Chirag Raval

#### Attachments

• 397.9 KB Views: 2

#### 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?
Test this sample...

#### Attachments

• 75.7 KB Views: 7

#### 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 …

#### Chirag R Raval

##### Member
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.

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"

- Destination worksheet : "REQUIREMENT".

As any header can be easily found with the child level MATCH
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

##### Member
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" "

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?

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 …

#### Chirag R Raval

##### Member
Dear Sir @ Marc L,

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

(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

(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

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

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.

hope you help sir,

Regards,

Chirag Raval

#### Chirag R Raval

##### Member
Dear Sir @vletm ,

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.

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

#### Chirag R Raval

##### Member
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 .