Monthly report requiring pre-formatted fields

excel01noob

Board Regular
Joined
Aug 5, 2019
Messages
93
Office Version
  1. 365
  2. 2016
Dear excel colleagues

I have a monthly report which is completed during the current month by people with some key data, basically "customer account with format A1234," purchase Id" and if "fully paid" or "instalments" in three different columns.

In the other columns data is almost the same, only exception is if customer account starts as A9... Then data in column K "sales person ID" is different (instead of 1000 it should be 2000).

We also have a column with header "date" but the data in that column should be always the last business day of the past month (file is uploaded at the beginning of the next month).

How should I create a macro that automatically fills the data in columns that don't change by replicating to the last line where customer id, purchase id and payment is?

Thank you in advance
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
That description is pretty hard to follow for somebody who is not familiar with your worksheet(s).
Any chance you could post a small set of dummy data and the expected results?
My signature block below has a link that has some good methods for doing that.
 
Upvote 0
the last line where customer id, purchase id and payment is

Identifying correct row
- which line is that ?
- which combination of values makes it the row to be transferred to the new worksheet ?

Column details
- what is the range of columns A to ??
- which columns contain static values ? ( ie contain values to be copied to new worksheet)
 
Upvote 0
Appl. IDtypeversionCustomer accountPurchase IDPaymentQuantityValid FromValid ToMaterialSales person IDDistrib. Party
50500102A70163NR1713632849fully paid328/06/201928/06/20191133051100001
50500102A84459NR1118024832fully paid2528/06/201928/06/20191133051100001
A15871NR1813144356instalment428/06/201928/06/20191133051
50500102A96131NR1962598303fully paid1628/06/201928/06/20191133051200001
A13121NR1599689442instalment928/06/201928/06/20191133051

<tbody>
</tbody>


Something like this
Column D, E, F, G is inserted by each worker.
the others are all the same and must be replicated.
I deleted the data on cell A4,B4,C4,K4,L4 which must be the same as previous ones

Column H and I it always the last business day of the month (here I used June 2019 as example).

Column K is always 1000 unless under cell D.. it starts by A9... where K... is 2000
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top