Convert Many Invoice Formats to a Standard Format

[SamT]

So to start I need to get this conversion making the credit identification number the same as the invoice number with a 9 in front of it working soon as possible.

For each BizName in Returns
Loop thru the Credit BizName cells and if Credit BizName = Return BizName Then if Credit BizName Cell.offset (to Date) = Return Date, then Return Invoice Number = "9" & Left(Credit BizName.offset (to Invoice), n) & Return Invoice Number

[SamT]

1. All the data files are emailed to me. Currently I save them in different places.
                                       C:\FlexibakeConversions\Data
                                       C:\Albany\data
                                       C:\midstate\data                     
                                       C:\ahold\Data
                                       C:\kkb\data
2.    C:\midstate\data\08.15.20_Recap.xlsx
       C:\ahold\Data\0056919000647_0004353672.csv
       C:\FlexibakeConversions\Data\HEIDELBERG-INVCR092220.csv
3.    .xlsx
       .csv

Not a bad file system, but I strongly recommend that you reverse the Folder names and place all inputs in subfolders of "Data". That would make it much easier to code and easier to backup and easier to move when the time comes. You can also think about adding a SubSubFolder "Archive" to each SubFolder

Other than the editorializing in re Locations, note the details below

Client	Data Store Location	File Name Patteren	File Type
Flexibake	C:\Data\Flexibake	HEIDELBERG-INVCR & 5 digit number	csv
Albany	C:\Data\Albany	?	?
midstate	C:\Data\midstate	DateFormat("mm.dd.yy") & _Recap	xlsx
ahold	C:\Data\ahold	13 digit numer & _ & 11 digits number	csv
kkb	C:\Data\kkb	?	?
?	C:\Data\?	?	?
?	C:\Data\?	?	?

Can you fill in the blanks above So I can start a Workbook for this Project? Also tell me what you intend to do with the Location directory tree.

[joeny0706]

Albany and KKB both come out of the same file. I need to filter the raw data in file Heidelberg KKB 090620-091220. In column C I select only chain 5050000. That is the data for albany.
To get the kkb data I need to deselect 5050000 and that is the kkb data.

Client	Data Store Location	File Name Patteren	File Type
Flexibake	C:\Data\Flexibake	HEIDELBERG-INVCR & ddmmyy	csv
Albany	C:\Data\Albany	Heidelberg KKB ddmmyy-ddmmyy	?
midstate	C:\Data\midstate	DateFormat("mm.dd.yy") & _Recap	xlsx
ahold	C:\Data\ahold	13 digit numer & _ & 11 digits number	csv
kkb	C:\Data\kkb	?	?
Client	Data Store Location	File Name Patteren	File Type
Flexibake	C:\Data\Flexibake	HEIDELBERG-INVCR092220	csv
Albany	C:\Data\Albany	Heidelberg KKB 090620-091220	csv
midstate	C:\Data\midstate	08.15.20_Recap	xls
ahold	C:\Data\ahold	0056919000647_0004353672	csv
kkb	C:\Data\kkb	Heidelberg KKB 090620-091220	csv
vbc	C:\Data\vbc	VBC HB090620-091220	xlsx

I created the structure like you recommended. The above is the new tree created.
The flexibake raw data is the only one that I need to edit the credit identification numbers as explained in this thread.

[SamT]

Is there any rhyme or reason to ahold's File name digits? I am trying to determine the best way to discover the latest submissal. Is there only one file at a time in a Data Storage Folder? Should I be looking at Date Created or Date Modified attribute?

The best possible situation (for VBA Express) is that all files are moved to an Archive folder as soon as they are processed. YMMV

I have started Joeny_Project.xlsm and am awaiting news in re ahold's File name and the latest submissal

[joeny0706]

That is what it is named when they send it to me. I get them all from email. I can easily name them anything I want when saving them.

I normally find them by the date modified column within Microsoft explorer.

I will create archive folder within each company data folder.

The vbc and kkb raw data files are emailed to me within one email. I already planned to save the kkb in one folder and vbc in another. That is a new plan since I am following your folder tree suggestion.

The midstate data is normally email in groups of 10 raw data xls files. Each file is a week of information.

I can save them in different places and also give them any name if that will make things better.'


A couple facts about the current process. I just wanted to mention I currently have a lookup file for each company conversion xslm file. Each lookup file has the item numbers of the raw data and then the item numbers I need they to match. I will attached a couple of the current xlsm files I use. I though this might be good to let you know about. Then I will need to know if I need to combine all the lookup files into one.

Attached are the albany conversion "xlsm" and lookup files I use now. I also attached the midstate conversion "xlsm" files and lookup files. This is just to show you what I am doing now, and explain I different lookup files for each xlsm. That probably not the most efficent but is how it is done now.

I also attached one of midstates converted files. This is how it needs to be so I can upload into the accounting program


SamT
Thanks for all the help and advice along the way.

[SamT]

I normally find them by the date modified column within Microsoft explorer.

I will create archive folder within each company data folder.

Great! Then we just need to Process all new Files, then move them into Archives. This also takes care of the multiple midstate files. As well as handling things anytime someone forgets to Process the files.

It also eliminates the need for File Names and Name Patterns.

Business Rules question: Does it matter, if midstate files are processed in bulk, that sometimes your Outputs don't have any midstate Data and other times they're full of it?

[joeny0706]

No it does not matter. I am not understanding the reason for your question but I have processed more than 6 months of data at once. I have not had any errors in any part of the process.

[joeny0706]

I read over your last question and wanted to add some info. When I do the conversions at times I add all the converted raw data together for the upload. When I do midstate I always combine all the weeks into one excel file before doing anything else

The only raw data that will need to be done separate is the flexibake. Since that is the only one I need to fix the credit numbers. All the other raw data can be combined for the conversion and for the upload.

[joeny0706]

Samt




How are things going.

I have one task that I will need to do also. I have 7 different stores that I need to delete out before converting. They need to be removed before going into the accounting program. I have a module that deletes any rows with 0 in the qty column. What I would do is alter that module to delete any rows that have these stores in the name column. Since I think you are working on a new process to do all my converting I wanted to see if that is something I will need to do "create a module to delete these names" or will that be a task the new process will be able to do?

Also do you have any questions or anything I can do to help?

Attached are the store names that I will need to delete before doing the converting. This is only with the flexibake raw data. I will not need this task for any of the other raw data.

[SamT]

I will add a Stores_To_Ignore Table to the Project From your attachment


My current thought is to import individual Raw Data Reports, filter them all into the proper Invoice and Return sheets, then adjust those into the final Accounting Reports. IOW, do the whole thing in one Swell Foop.

So far, I have a folder with 35 of your uploads, I am trying to sort thru to filter out what is needed for the Project

Do you have any more Business Rules I need to consider?

[SamT]

Oh, Yeah! I need copies of the two "Templates"

[joeny0706]

When you say add Stores_To_Ignore. Will that remove the complete row with that store?

There are a couple added fields I need to depend on other factors. You should be able to see these from my macros in the xlsm files I provided.
Ex
Column J on the converted files has whats called a class. The class all depends on the type of customer it is. All Chains stores "The customer name will start with a 1 "ex 1Hannaford:Ha367 Albany" has a class of "1 sales". The line from the macro "AddClassAll" shows them
"=IF(LEFT(RC[-7],1)=""1"",""1 Sales"",IF(LEFT(RC[-7],1)=""2"",""6 Institutional"",IF(LEFT(RC[-7],1)=""3"",""5 Rest"",IF(LEFT(RC[-7],1)=""4"",""1 Sales"", """"))))"
Then for the credits all the class is "2 Returns"

When there is a discount it has a field called line class. The midstate conversion files has a discount macro. For discounts they have a column called "line class". This is shown in the midstate invoices converted.xlsx file.
There is also a column "K" called template. The temple is determined by the class. If the class is 1 Returns the template is "Copy of:Intuit Service Invoice" If the class is a 2 returns the template is Custom Credit Memo

The invoice conversion xlsm file MidstateInvoice.xlsm has the discount macro. Not all of the xlsm have the discount macro.
Another factor to consider is if the qty column has a 0 the row will need to be removed. Not all the xlsm files have the macro ZeroRemoveH. Some of the raw data does not include 0 in the qty column. When there is a 0 in qty column that row needs to be removed.

You can see how these fields are all created and what I need by looking at all the macros. Would you also like me to explain how the macros all work in detail or are you looking at the macros and they can show what is needed?

Again I appreciate all the work you are doing and helping me to create these tools.

Thanks

[joeny0706]

There are no templetes. That is just a column and in that column it needs to state what template is used. That tells the program I use to upload the data into the accounting program what templete to use.

[SamT]

I have this Table in the Project Workbook. Please Fill in the blank Client & SubFolder info for Indepentants(sic)

Client	Store Data SubFolder	File Name With Pattern	Pattern	File Type
Flexibake	Flexibake	HEIDELBERG-INVCR&Pattern	"mmddyyyy"	csv
Albany	Albany	Heidelberg KKB &Pattern	From To "mmddyy-mmddyy"	csv
midstate	midstate	Pattern&_Recap	"mm.dd.yy"	xls
ahold	ahold	0056919000647_0004353672		csv
kkb	kkb	Heidelberg KKB &Pattern	From To "mmddyy-mmddyy"	csv
vbc	vbc	VBC HB&Pattern	From To "mmddyy-mmddyy"	xlsx
		Indepentents		csv

[Paul_Hossler]

This has grown a little bit since the first request:

So I would like to find a way to get those credits have the same invoice number as the invoice that was created on the same day for that store with the number 9 in front of it. I was thinking if a script could find where the store name and date of an invoice match the same store name and date of the credit note then populates column B with the invoice number with 9 if front of it. Then the number on the paper print out will match the number I import into the accounting program.

If I were doing it, I'd suggest that it might be better to consider restarting/rethinking

1. using samples of all the data inputs (with data, not a bunch of macros that insert formulas)

2. data cleansing rules (if 'Size = blank, then make Size = N/A')

3. a 'database' with any format templates, translation rules ('if = "C" then make it "Credit")

4. the business rules ('delete this store', 'net invoices and credits'

5. final output format (again with data) and any associated rules for making the output (file format, sort order, data formatting)


That way you could ...

IOW, do the whole thing in one Swell Foop.

Just my 2 cents

[joeny0706]

I have different replace data files. These are what to replace the fields with. This I am adding new stores and items to all the time. I will attach the different files. You can also explain how to add new stores for converting in your final xlsm file and I can add it all.
Each xlsm file has a replace invoice data and replace returns data file.
The data Ahold only has invoices. No creidts with them.

[joeny0706]

Paul

My original/main task was to fix the credit issues I am having and stated in the first post. SamT has looked over my process and agreed it is not the most efficient and best way to do this all. I did not think anyone would be willing or want to help me redo this complete process. There is a lot involved in converting the data. The process does work how it is but not the best way.
I have this all the time. When a client ask to fix a problem. Say there server is having an issue. I tell them the sever works but it is time to replace it. They tell me while it is working so lets leave it alone. That always bothers me. In this case I was the one saying it works how it is so its fine. I have been lucky and SamT has been willing to help me recreate the complete process. I am willing to do anything needed to help complete this process.

I am going to be deploying the handhelds very soon. That is when I need the credit number fixed with a 9 in front of it. If I have a new way to convert all the data and at the same time fix the credit number that would be great. Most important is to have the credit issue fixed.


SamT

I did want to state again that the flexibake data is the only raw data where I need the credit number changed into the invoice number with a 9 in front of it. All the other raw data has the correct credit number and does not need to be changed.

[joeny0706]

I have this Table in the Project Workbook. Please Fill in the blank Client & SubFolder info for Indepentants(sic)

Client	Store Data SubFolder	File Name With Pattern	Pattern	File Type
Flexibake	Flexibake	HEIDELBERG-INVCR&Pattern	"mmddyyyy"	csv
Albany	Albany	Heidelberg KKB &Pattern	From To "mmddyy-mmddyy"	csv
midstate	midstate	Pattern&_Recap	"mm.dd.yy"	xls
ahold	ahold	0056919000647_0004353672		csv
kkb	kkb	Heidelberg KKB &Pattern	From To "mmddyy-mmddyy"	csv
vbc	vbc	VBC HB&Pattern	From To "mmddyy-mmddyy"	xlsx
		Indepentents		csv

Independents are a type of customer. There is no raw data that comes from independents. All the raw data has customers that are independents. I am not sure if I am understanding you questions correctly.

[snb]

@SamT

I thnik you'd better apply for a Job at Joeny's.
I don't think Joeny can tell you what the process in total should look like.
He has been given a task that hardly matches his comprehension and a tool that has been designed by somenone else who hardly knew any VBA.
Like PH suggested: redesigning should be done first.

[joeny0706]

snb

That is correct. I created the xlsm files by asking different questions to different people in this forum. I put together the ones that worked to get what I need. I do hardly know any VBA. When I first started this task was the first time I ever used VBA. When I was hired this was not part of the job description. When I was asked if it can be done I did get it to work in a timely manner. I knew it was not the best and most efficent way but In the end it does get me the correct formatted data so I am able to upload it into the accounting program and it is all correct in the end.

What takes me 3 hours a week has been able to remove 4 data entry employees by getting this to work. So my employer see what I have done as a great job.