Update Master list of open/close hours from weekly updated list

R

rvelt1213

New Member
Joined
Apr 13, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I have a Master list of about 4000 different store open and close hours. It is pretty simple with a column each for store #, store name, open time Mon, close time Mon, open time Tue, close time Tue, etc.

Twice a week, there is a small update of hours for, let's say approx 30-50 stores that then needs to be updated in the Master list

How can I automate my Master list to update to the new hours? I'm thinking I will need to use a VBA, but am not having any luck finding examples. Open to formula suggestions too.

Please let me know if more information is needed and thanks in advance for any suggestions!


While we're at it - is there a way to convert 9am to 9 AM without Excel auto changing it to read 9:00 AM. I have tried changing to text - this does not work. Right now I am adding a column, changing to Text, entering how I want it to look, flash fill cells, then delete original column. It would be much easier to do a find and replace if possible, but I need it in the correct format.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Well, english is not my native language, therefor I keep it short and smart (hopefully ;) )

Regarding the time question.
Is this what you ar looking for (see pictures, also): text formatting or the userdefined formatting "h AM/PM"?
 

Attachments

  • text_formatting (h AM_PM).jpg
    text_formatting (h AM_PM).jpg
    10.6 KB · Views: 21
  • userdefined_formatting (h AM_PM).jpg
    userdefined_formatting (h AM_PM).jpg
    86.6 KB · Views: 20
Upvote 0
How does the update list looks like: store#; weekday; opening; closing or how?

The best would be, you will post pictures of a part of the master list and a part of the update list; both with row- and column-headings.
Then I will look for, if I can offer you a solution and with/without vba.
 
Upvote 0
The custom time formatting worked perfect! Thank you for that!! (Although I wasn't sure about Zahlen and Benutxerdefiniert ?)

Here is a small sample of the spreadsheets. I would like the "Master" (1st mini sheet) to be updated from the "Weekly Update" (2nd mini sheet). For example: notice F12 on Master (closing time for store #1728) is "9 PM", but needs to be changed to F4 "7 PM" on Weekly Update. Please let me know if you need additional information.

I appreciate your time!

Sample of Master list:
Sample for Mr Excel.xlsx
ABCDEFGHIJ
1STORETYPE# SHEETSCurrent PIC ListedM-F OPENM-F CLOSESAT OPENSAT CLOSESUN OPENSUN CLOSE
23SC59 AM9 PM9 AM7 PM10 AM6 PM
39SC59 AM9 PM9 AM7 PM10 AM6 PM
413SC59 AM9 PM9 AM7 PM10 AM6 PM
580SC59 AM9 PM9 AM7 PM10 AM6 PM
6152SC59 AM9 PM9 AM7 PM10 AM6 PM
7337SC59 AM8 PM9 AM7 PM10 AM6 PM
8550SC59 AM9 PM9 AM7 PM10 AM6 PM
9641SC59 AM9 PM9 AM7 PM10 AM6 PM
101013WM39 AM9 PM9 AM7 PM10 AM6 PM
111173SC59 AM9 PM9 AM7 PM10 AM6 PM
121728SC59 AM9 PM9 AM7 PM10 AM6 PM
131879WM39 AM9 PM9 AM7 PM9 AM6 PM
142773NHM39 AM9 PM9 AM7 PM10 AM6 PM
154475SC59 AM9 PM9 AM7 PM10 AM6 PM
165491SC59 AM9 PM9 AM7 PM10 AM6 PM
17450SC58 AM8 PM9 AM7 PM10 AM6 PM
18739SC58 AM8 PM9 AM7 PM10 AM6 PM
19879SC58 AM8 PM9 AM7 PM10 AM6 PM
2047SC59 AM7 PM9 AM7 PM10 AM6 PM
216879SC59 AM9 PM9 AM6 PM10 AM5 PM
228958NHM39 AM9 PM10 AM6 PM10 AM6 PM
231051SC59 AM7 PM9 AM6 PM11 AM5 PM
245879SC59 AM8 PM9 AM6 PM11 AM5 PM
25837WM39 AM7 PM9 AM6 PM10 AM6 PM
26116WM39 AM7 PM9 AM7 PMCLOSED
MASTER


Sample of Weekly Update:

Sample for Mr Excel.xlsx
ABCDEFGHIJ
1STORETYPE# SHEETSCurrent PIC ListedM-F OPENM-F CLOSESAT OPENSAT CLOSESUN OPENSUN CLOSE
24475SC509 AM9 PM9 AM7 PM10 AM6 PM
33594NHM309 AM9 PM9 AM7 PM10 AM6 PM
41728SC509 AM7 PM9 AM7 PM10 AM6 PM
55491SC509 AM9 PM10 AM7 PM10 AM6 PM
6879SC508 AM8 PM9 AM7 PM10 AM6 PM
73538SC509 AM8 PM9 AM7 PM10 AM6 PM
82720SC508 AM8 PM9 AM7 PM10 AM6 PM
92496SC509 AM7 PM9 AM6 PM11 AM5 PM
10724SC509 AM9 PM9 AM7 PM10 AM6 PM
11903SC508 AM8 PM9 AM7 PM10 AM6 PM
122277SC509 AM9 PM9 AM7 PM10 AM6 PM
135292SC509 AM9 PM9 AM7 PM10 AM6 PM
14630SC508 AM9 PM8 AM7 PM10 AM6 PM
15708SC508 AM8 PM9 AM7 PM10 AM6 PM
WEEKLY UPDATE
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1Cell ValueduplicatestextNO
A15Cell ValueduplicatestextNO
A14Cell ValueduplicatestextNO
A13Cell ValueduplicatestextNO
A12Cell ValueduplicatestextNO
A11Cell ValueduplicatestextNO
A10Cell ValueduplicatestextNO
A9Cell ValueduplicatestextNO
A8Cell ValueduplicatestextNO
A7Cell ValueduplicatestextNO
 
Upvote 0
rvelt1213 said:
The custom time formatting worked perfect! Thank you for that!! (Although I wasn't sure about Zahlen and Benutxerdefiniert ?)

Here is a small sample of the spreadsheets. I would like the "Master" (1st mini sheet) to be updated from the "Weekly Update" (2nd mini sheet). For example: notice F12 on Master (closing time for store #1728) is "9 PM", but needs to be changed to F4 "7 PM" on Weekly Update. Please let me know if you need additional information.

I appreciate your time!

Sample of Master list:
Sample for Mr Excel.xlsx
ABCDEFGHIJ
1STORETYPE# SHEETSCurrent PIC ListedM-F OPENM-F CLOSESAT OPENSAT CLOSESUN OPENSUN CLOSE
23SC59 AM9 PM9 AM7 PM10 AM6 PM
39SC59 AM9 PM9 AM7 PM10 AM6 PM
413SC59 AM9 PM9 AM7 PM10 AM6 PM
580SC59 AM9 PM9 AM7 PM10 AM6 PM
6152SC59 AM9 PM9 AM7 PM10 AM6 PM
7337SC59 AM8 PM9 AM7 PM10 AM6 PM
8550SC59 AM9 PM9 AM7 PM10 AM6 PM
9641SC59 AM9 PM9 AM7 PM10 AM6 PM
101013WM39 AM9 PM9 AM7 PM10 AM6 PM
111173SC59 AM9 PM9 AM7 PM10 AM6 PM
121728SC59 AM9 PM9 AM7 PM10 AM6 PM
131879WM39 AM9 PM9 AM7 PM9 AM6 PM
142773NHM39 AM9 PM9 AM7 PM10 AM6 PM
154475SC59 AM9 PM9 AM7 PM10 AM6 PM
165491SC59 AM9 PM9 AM7 PM10 AM6 PM
17450SC58 AM8 PM9 AM7 PM10 AM6 PM
18739SC58 AM8 PM9 AM7 PM10 AM6 PM
19879SC58 AM8 PM9 AM7 PM10 AM6 PM
2047SC59 AM7 PM9 AM7 PM10 AM6 PM
216879SC59 AM9 PM9 AM6 PM10 AM5 PM
228958NHM39 AM9 PM10 AM6 PM10 AM6 PM
231051SC59 AM7 PM9 AM6 PM11 AM5 PM
245879SC59 AM8 PM9 AM6 PM11 AM5 PM
25837WM39 AM7 PM9 AM6 PM10 AM6 PM
26116WM39 AM7 PM9 AM7 PMCLOSED
MASTER


Sample of Weekly Update:

Sample for Mr Excel.xlsx
ABCDEFGHIJ
1STORETYPE# SHEETSCurrent PIC ListedM-F OPENM-F CLOSESAT OPENSAT CLOSESUN OPENSUN CLOSE
24475SC509 AM9 PM9 AM7 PM10 AM6 PM
33594NHM309 AM9 PM9 AM7 PM10 AM6 PM
41728SC509 AM7 PM9 AM7 PM10 AM6 PM
55491SC509 AM9 PM10 AM7 PM10 AM6 PM
6879SC508 AM8 PM9 AM7 PM10 AM6 PM
73538SC509 AM8 PM9 AM7 PM10 AM6 PM
82720SC508 AM8 PM9 AM7 PM10 AM6 PM
92496SC509 AM7 PM9 AM6 PM11 AM5 PM
10724SC509 AM9 PM9 AM7 PM10 AM6 PM
11903SC508 AM8 PM9 AM7 PM10 AM6 PM
122277SC509 AM9 PM9 AM7 PM10 AM6 PM
135292SC509 AM9 PM9 AM7 PM10 AM6 PM
14630SC508 AM9 PM8 AM7 PM10 AM6 PM
15708SC508 AM8 PM9 AM7 PM10 AM6 PM
WEEKLY UPDATE
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1Cell ValueduplicatestextNO
A15Cell ValueduplicatestextNO
A14Cell ValueduplicatestextNO
A13Cell ValueduplicatestextNO
A12Cell ValueduplicatestextNO
A11Cell ValueduplicatestextNO
A10Cell ValueduplicatestextNO
A9Cell ValueduplicatestextNO
A8Cell ValueduplicatestextNO
A7Cell ValueduplicatestextNO
Click to expand...
Here is a solution without VBA
I added in the master-sheet supporting columns (see picture) with one (1) formula, as shown in the formula input field.
Because i do have a german office version, i GUESS you have to exchange some commands as shown below.
I also put a conditional formatting (orange) on the supporting cells, to point the changes.
What you have to do, is, copy the supporting cells and paste as values over the existing cells.
Please let us know, if this works as you want.
Kind regards,
Berny
=IFERROR(INDEX('WEEKLY UPDATE'!$A$2:$J$15;MATCH($A2;'WEEKLY UPDATE'!$A$2:$A$15;0);MATCH(E$1;'WEEKLY UPDATE'!$A$1:$J$1;0));E2)
 

Attachments

  • weekly_updates in master.png
    weekly_updates in master.png
    65.5 KB · Views: 23
Upvote 0
Bernst said:
Here is a solution without VBA
I added in the master-sheet supporting columns (see picture) with one (1) formula, as shown in the formula input field.
Because i do have a german office version, i GUESS you have to exchange some commands as shown below.
I also put a conditional formatting (orange) on the supporting cells, to point the changes.
What you have to do, is, copy the supporting cells and paste as values over the existing cells.
Please let us know, if this works as you want.
Kind regards,
Berny
=IFERROR(INDEX('WEEKLY UPDATE'!$A$2:$J$15;MATCH($A2;'WEEKLY UPDATE'!$A$2:$A$15;0);MATCH(E$1;'WEEKLY UPDATE'!$A$1:$J$1;0));E2)
Click to expand...
Thank you so much Berny! The formula is throwing an error for me but I plan to work on it tomorrow. I think I can make this work though! Should the spreadsheet attachment be interactive? When I click on it, it disappears.
 
Upvote 0
rvelt1213 said:
Thank you so much Berny! The formula is throwing an error for me but I plan to work on it tomorrow. I think I can make this work though! Should the spreadsheet attachment be interactive? When I click on it, it disappears.
Click to expand...
Hello rvelt1213,

hmmm, without a screenshot it's difficult to evaluate where the failure is located, so I just can guess one or two possibilities:
Did you copy&paste my formula or did you typed it by yourself and did you see, the little upper commata infront and behind the table-name?
Did you replace WEEKLY UPDATE (that's the name of the table where I have the updates) to the table-name where your updates are stored?

"Spreadsheet attachement":oops: My native language is german, and I do not have any idea what a spreadsheet attachement is, even I do know what each of this words mean.
Please explain it to me, maybe I can answer then, if it should be interactive or not.

Kind regards,
Berny
 
Upvote 0
I just figured it out!!! Excel did not like the semi-colons ; I replaced them with commas , and it worked perfect!

Haha! Sorry about the confusion! I meant the mini-spreadsheet (see screenshot). I thought you should be able to copy and paste from them, but I cannot.

1653407825249.png
 
Upvote 0
A
rvelt1213 said:
I just figured it out!!! Excel did not like the semi-colons ; I replaced them with commas , and it worked perfect!

Haha! Sorry about the confusion! I meant the mini-spreadsheet (see screenshot). I thought you should be able to copy and paste from them, but I cannot.

View attachment 65454
Click to expand...
Ah, you mean
=IFERROR(INDEX('WEEKLY UPDATE'!$A$2:$J$15,MATCH($A2,'WEEKLY UPDATE'!$A$2:$A$15,0),MATCH(E$1,'WEEKLY UPDATE'!$A$1:$J$1,0)),E2)
works fine!?

Oh, I just translated the commands... So there is, additional to the command-language, another difference between the english and the german excel, what mean, I have to "translate;)" in my formulas the semicolons into commas, too - good to know and thanks for this response.
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

S
  • Question Question
Is there a way to open a workbook, refresh everything and close after success.
Replies
1
Views
590
severynm
severynm
G
  • Question Question
Using a set date to prevent access to Excell tabs and macros
Replies
1
Views
180
Micron
M
S
  • Question Question
Updating master list from multiple tabs
2
Replies
17
Views
788
sampson11
S
B
  • Question Question
Event code to populate message upon workbook open at specified dates
Replies
2
Views
176
bh24524
B
J
  • Question Question
Compare 2 Workbooks and add only the New Information to a Master Sheet
Replies
0
Views
249
jpaul123
J

Forum statistics

Threads
1,223,270
Messages
6,171,103
Members
452,379
Latest member
IainTru

Share this page

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