Data validation by row

**zextrot** · 07-27-2017, 02:46 PM

Cross-posted @ http://forum.chandoo.org/threads/dat...-by-row.35190/
Cross-posted @ https://www.mrexcel.com/forum/excel-...ation-row.html

Data validation by row - the purpose of this macro is to 'clean up' a data set by analyzing each row, then submit the data set to an email address. Attached file "data dump" is an example of an unclean data set. Attached file "data validation by row" is the cleaned up result we are trying to achieve.

1. to either delete the row or to keep the row based on if it includes one of a list of possible values:
- valid date
- order:
- any of the numerical values 12345 thru 12363 as shown in the result file rows 3-21

2. to pick out the purchase order number which will occur after keyword "order:"

3. name the file using the purchase order number as the name, automatically email the workbook to an email address and close the workbook without saving.

4. to include a particular value "fakename" in the subject line of the email.

**jaslake** · 07-29-2017, 06:16 PM

Hi Andrew

Will ALL of these rows that contain the numerical values 12345 thru 12363 ALWAYS contain "EA"...if Yes...will ANY other Rows contain "EA"?
Will these numerical values 12345 thru 12363 EVER be different?
Do you use Outlook as the Email Client?
Where does the Email Address come from..."Email ap@fakenamecorp.com"?
Which Date should be operative...your Sample File has two?
Where does this come from... "include a particular value "fakename" in the subject line of the email"?

I'll have more questions...

**zextrot** · 07-31-2017, 09:19 AM

Will ALL of these rows that contain the numerical values 12345 thru 12363 ALWAYS contain "EA"...if Yes...will ANY other Rows contain "EA"?
>No they will not always contain EA.

Will these numerical values 12345 thru 12363 EVER be different?
>Yes, it could be any numbers and change from one day to the next. These are customer part #s. Each customer will have their own set of unique numbers.

Do you use Outlook as the Email Client?
>Yes.

Where does the Email Address come from..."Email ap@fakenamecorp.com"?
>This is the customer's email where the "order" is coming from.

Which Date should be operative...your Sample File has two?
>The first date.

Where does this come from... "include a particular value "fakename" in the subject line of the email"?
>This is so the email that is sent will be easily recognized by the system reading the email.

**jaslake** · 07-31-2017, 10:56 AM

Hi Andrew

I'm looking for things that are constant...

Will the Customer Part Number EVER contain non-numeric characters?
Will the Customer Part Number ALWAYS contain 5 digits?
Will the Customer Part Number ALWAYS be in the 3rd position with a space before and after? If yes, will there EVER be spaces within the Customer Part Number?

Where do I find this...

This is the customer's email where the "order" is coming from.

**zextrot** · 07-31-2017, 11:25 AM

Ultimately I want this macro to be used for many different customers so I was hoping for a fairly dynamic set of code that I could modify a little for each customer's idiosyncrasies.

However, to answer your questions .. This particular customer will number their lines as you can see in the part number rows they are numbered 1,2,3,4,5 etc with a space, then a longer number, then a space then the customer's part #. And no, the customer's part# will never have spaces within it and will never have letters, only numbers.
Yes this customer will always order in EA so every part# row will have EA in it.

**jaslake** · 07-31-2017, 11:40 AM

Hi Andrew

I have Code that will work for THIS particular Customer. Please attach two or three Data Dump Files that vary from the format of THIS particular Customer File so I can see the differences...use Actual Data if you can...change any identifying information (Customer Name, Customer Number, email addresses, etc.)...

1 66666666666 12345 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
2 66666666666 12346 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
3 66666666666 12347 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
4 66666666666 12348 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
5 66666666666 12349 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
6 66666666666 12350 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
7 66666666666 12351 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
8 66666666666 12352 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
9 66666666666 12353 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
10 66666666666 12354 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
11 66666666666 12355 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
12 66666666666 12356 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
13 66666666666 12357 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
14 66666666666 12358 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
15 66666666666 12359 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
16 66666666666 12360 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
17 66666666666 12361 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
18 66666666666 12362 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00
19 66666666666 12363 5000 EA spec stuff, Metal, Type x7, 4−1/2" x .090" x 7/8" $0.99 $2,100.00

**zextrot** · 07-31-2017, 06:45 PM

In the attached files ...

Example 1: straightforward example
Example 2: broken lines (rows 43-44 shows where part of the line above got cut off and dumped into these rows)
Example 3: in this example, the order was more than one page long and you can see page two rows 80-82

**jaslake** · 07-31-2017, 07:51 PM

Hi Andrew

As far as I can see every Raw Data File you've uploaded has this characteristic...

Will ALL of these rows that contain the numerical values xxxxx thru xxxxx ALWAYS contain "EA"

But you've told me this...show me a Raw Data File that meets this...

No they will not always contain EA.

I think I know what needs done...no guarantees...

**zextrot** · 08-01-2017, 08:56 AM

The answer I initially gave was based on speaking of universal code. This specific customer will always order with EA.

**jaslake** · 08-01-2017, 10:55 AM

Hi Andrew

Let's try this again...

Will ALL rows of ALL Raw Data Files that contain the numerical values xxxxx thru xxxxx ALWAYS contain "EA".

If "Yes" then end of discussion...
If "No" then please attach a Raw Data File that exhibits this...

**zextrot** · 08-01-2017, 11:18 AM

yes, for this customer all rows with the numerical value range will always contain EA

**jaslake** · 08-01-2017, 01:20 PM

Hi Andrew

Let's try this again...

Forget about this Customer...do you have ANY Customers that will not have EA in some or all their Records...if yes, please show me an example of each (some records without EA...all records without EA).

**zextrot** · 08-01-2017, 01:33 PM

I hesitate to answer because these sample sheets are based on this particular customer. Other customers' sheets would be very different. If they did use EA it would be in a different spot and not necessarily within so many characters of the part#. I imagine each customer would take a fresh set of eyes to determine if the same approach is appropriate.

The EA is a unit of measure. Others that get used might be PC, PAC, PACK, BX, BOX, CS, CASE but as mentioned above, the rest of the sheet would be formatted so differently among different customers.

For this customer, the EA will be used every time.
When we get a working code for this customer, the next conversation might be more productive in terms of differences in the code for each.

**jaslake** · 08-01-2017, 01:44 PM

Hi Andrew

Sorry Andrew, I misunderstood...so, you're currently looking at a solution for ONLY this Customer...now your responses make sense. I'll get back on it.

**zextrot** · 08-01-2017, 01:48 PM

yes, once we started talking I deferred to your assessment that we should be focusing on just this customer first

**jaslake** · 08-01-2017, 03:00 PM

Hi Andrew

Sorry about the miss-communication...what I was and am asking is to see what the differences are in all your various Customer Data Dumps so I can access the Constants in the Structure of the different Customer Files.

I'll be wrapping up the Code for your initial Data Dump probably this PM...perhaps tomorrow AM and will post it for your review.

**jaslake** · 08-02-2017, 11:57 AM

Hi Andrew

This Code in the attached appears to work on the Data Dump Files you uploaded. It will NOT work on Data Dump Files that don't have "EA" in the Part Number Line Items. You'll need to upload Data Dump Files that exhibit that Structure.

Place all the attached Files in the same Folder. Open Code Book v3.xlsm, click the Button, select a Data Dump File.

Please Login or Register  to view this content.

**zextrot** · 08-03-2017, 10:49 AM

Thank you, this appears to work for this customer very well.

I have a couple of questions:
1. Curious if the 'button and select file' procedure has an advantage over pasting the code into a macro. (One advantage that comes to mind is the user may not have to have developer option turned on to run this? What advantages were you thinking when you chose to do a button like this?)
2. How would we make the email automatically send?

**jaslake** · 08-03-2017, 11:01 AM

Hi Andrew

I don't understand your question...

Curious if the 'button and select file' procedure has an advantage over pasting the code into a macro

The Code is in a Macro in the Code Book...the Button Fires the Code.

Regarding this...

How would we make the email automatically send?

In Module 1, Sub Email_Sheet(Email As String, PoNo As String, Subj As String) change .Display to .Send

Please Login or Register  to view this content.

**zextrot** · 08-03-2017, 11:19 AM

Typically I would put the code into a module and run a macro from the macro list.
Here you have the code in a separate code book and hit a button which brings up a dialog box to choose a file. I was just curious what advantages this method might have.

**zextrot** · 08-03-2017, 02:11 PM

In process of testing this code. I have found one issue. I don't think I communicated correctly regarding the email address.
The email is set up to send to the email address pulled off the spreadsheet data dump. That is the email address of the customer. We actually want to be able to input an email address into the code where the email will send. It will be our own email address.

**jaslake** · 08-03-2017, 03:55 PM

Hi Andrew

The advantage of having the Code in a "Code Book" is that you don't need to have the Code in each Data Dump File. You could, if you so choose, place the Code in each User's Personal.XLSB.

Regardless of where the Code resides you can still do this (with modification) if you so choose...

Typically I would put the code into a module and run a macro from the macro list.

**zextrot** · 08-03-2017, 04:00 PM

I like the code book, it's cool.
If you can help with the above testing issue I think this code will pass just fine.

**jaslake** · 08-03-2017, 04:02 PM

Hi Andrew

I asked you this back in my Post #2 and received no reply (I think)...

Where does the Email Address come from..."Email ap@fakenamecorp.com"?

If you have a Static Email Address it can simply be inserted in the Code here...

Please Login or Register  to view this content.

**zextrot** · 08-03-2017, 04:08 PM

The email address I am inputting is giving me a compile error: Expected: end of statement.
Would this be because the email address has a dash/hyphen in it?

Ex: someone@abc-xyz.com

NEVERMIND .. I did not have the quotations.

**jaslake** · 08-03-2017, 06:55 PM

Originally Posted by zextrot

The email address I am inputting is giving me a compile error: Expected: end of statement.
Would this be because the email address has a dash/hyphen in it?

Ex: someone@abc-xyz.com

NEVERMIND .. I did not have the quotations.

Glad you got it sorted...

**jaslake** · 08-03-2017, 09:20 PM

Hi Andrew

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

**zextrot** · 08-04-2017, 09:02 AM

Yes sir i will do that right after testing which is hopefully today.
Then i will do a separate thread to discuss the dynamic potential of this code.
Your genius is appreciated.

**zextrot** · 08-09-2017, 10:17 AM

jaslake, i want to test a theory i have regarding the dynamic potential of this code.

1. How would the code change if we wanted to accept rows with EA or the phrase "Item Number"?
2. Can we do variable declaration to allow me to alter the code for other spreadsheets that may use other terminology? (Example: PC or "Part Number")

**jaslake** · 08-09-2017, 02:42 PM

Hi Andrew

You can try the Code in the attached...it's not been tested. I've added a Data Validation Dropdown in Cell B3 for the User to select a Key Word.

**jaslake** · 08-09-2017, 06:45 PM

Hi Andrew

You'd probably be better served by uploading Raw Data Files that exhibit different scenarios so that we can see what the similarities are...your choice...

**zextrot** · 08-14-2017, 02:21 PM

The dropdown you mentioned in msg #30 is to choose one of a list of values, EA or another choice. When using one value, I would like to be able to modify to whatever one value is unique for that customer.

Some customers' data sets get split up in the data dump, so to capture all the pertinent rows, I would like the option of two values .. i.e., rows with EA are captured, plus rows with a different value as well, like "Item number" or "Part number" or whatever other value might be unique to that customer's data dump.

**zextrot** · 08-14-2017, 02:22 PM

If this explanation is not sufficient I can do some extra data files. I do have to "de-sensitize" the data which takes some time.

**jaslake** · 08-14-2017, 02:40 PM

Hi Andrew

I'm unable to test without Data Files...I'd only be guessing...I prefer to not guess.

**zextrot** · 08-14-2017, 06:29 PM

Hopefully these examples are sufficient to show the need. I want to be able to modify the code to fit the unique situations. The wording changes from one data set to the next, and sometimes an additional trigger in addition to "EA" is needed to grab the necessary rows.

CITY CORP
Date [row 3]
Purchase Order Number [row 4 has the phrase, row 5 has the number]
EA would grab rows 34 & 38

DENVER CORP
Date [row 2]
PURCHASE ORDER# [row 4 has the phrase, row 5 has the number]
EA would grab rows 17, 19, 21, 23

SOUTHERN CORP
Date [row 4]
P.O. Number: [row 3 has the phrase, row 13 has the number]
EACH would grab row 26
CASE would grab row 28

WOOD CORP
Date appears on rows that will already be grabbed so no need to grab date separately
PO Number: [row 34 has the phrase and the number]
EA would grab rows 9, 13, 17, 21
Item number [also needs to be grabbed, i.e., rows 10, 14, 18, 22]

**zextrot** · 08-15-2017, 11:21 AM

FYI .. i found a strange bug. Running the original macro you wrote on several orders from the same customer, the macro worked flawlessly on every one except one one data dump file it kept a row with the customer's PO number. This same line with their web address and PO number appears identically on all their POs, so I do not see a logical reason for this row to have been kept on one file but not the other files.

The below row is row 19 on several data sets, but the macro only kept it on one:
www.fakename.com Purchase order: P037009

**jaslake** · 08-15-2017, 12:38 PM

Hi Andrew

Looking at your disparate files nothing comes to mind...

I want to be able to modify the code to fit the unique situations

**jaslake** · 08-15-2017, 12:41 PM

Originally Posted by zextrot

FYI .. i found a strange bug. Running the original macro you wrote on several orders from the same customer, the macro worked flawlessly on every one except one one data dump file it kept a row with the customer's PO number. This same line with their web address and PO number appears identically on all their POs, so I do not see a logical reason for this row to have been kept on one file but not the other files.

The below row is row 19 on several data sets, but the macro only kept it on one:
www.fakename.com Purchase order: P037009

Attach the offending File...

**zextrot** · 08-15-2017, 01:22 PM

Originally Posted by jaslake

Hi Andrew

Looking at your disparate files nothing comes to mind...

Pinpointing the issue to number of triggers .. if it is not possible to have more than one trigger like the EA in the same macro, what about approaching it where the EA trigger is run in one subroutine to identify or even mark rows to be kept. Then a different trigger set up in a second subroutine. Then a third subroutine comes along and deletes all rows that aren't identified/marked.

**jaslake** · 08-15-2017, 01:42 PM

Hi Andrew

Multiple Triggers is not the issue...Code to handle these inconsistencies is the issue...
City Corp:

Purchase Order Number [row 4 has the phrase, row 5 has the number]

And...
Denver Corp:

PURCHASE ORDER# [row 4 has the phrase, row 5 has the number]

And (even worse)...
Denver Corp:

P.O. Number: [row 3 has the phrase, row 13 has the number]

Not to mention Wood Corp which has...
Purchase order in Line 30 and
PO Number: PO000123456 in Line 34

**zextrot** · 08-15-2017, 01:54 PM

Originally Posted by jaslake

Hi Andrew

Multiple Triggers is not the issue...Code to handle these inconsistencies is the issue...

Ok if we forget about one code to handle different scenarios and go back to the concept of separate subroutines each running a trigger, that would allow more than one trigger to be run on a single customer PO.

Regarding the inconsistencies in the wording of the triggers from one customer to another, I'm thinking I could manually modify the appropriate subroutines to fit the next customer I am working on.

**jaslake** · 08-15-2017, 02:07 PM

Hi Andrew

As I mentioned, Multiple Triggers is not the issue. The Issue is the inconsistencies in the Raw Data.

I believe you'd need to Modify the Raw Data...for example, City Corp...

Before:
City Corp_Before.jpg

After:
City Corp_After.jpg

**zextrot** · 08-15-2017, 02:10 PM

Oh .. you are saying that the data dumps have inconsistencies within the same customer? .. sometimes their PO number appears on the same row as the phrase and sometimes the number gets bumped to the next row

**jaslake** · 08-15-2017, 02:33 PM

This is an issue with the Code and how it locates the PO Number...

sometimes their PO number appears on the same row as the phrase and sometimes the number gets bumped to the next row

**zextrot** · 08-15-2017, 05:48 PM

Ok .. how about this approach:

1. No need to capture date or name the filename anything.
2. Subroutine to identify/mark rows x, y, z every time (each customer's data set consistently has their name/PO on certain rows. I can modify the code for city corp for example to identify rows 4, 5, and 13 every time. On Denver Corp, i would modify the macro to identify rows 4, 5 and 7 every time)
3. Subroutine to identify/mark any rows that might contain EA
4. Subroutine to identify/mark any rows that might contain BX
5. Subroutine to identify/mark any rows that might contain ITEM NUMBER
(i can manually add more or fewer of these subroutines and modify the phrase as needed for a particular customer)
6. Subroutine to only keep identified/marked rows.
7. Subroutine to email the file to an email address and close the file.

**jaslake** · 08-15-2017, 06:38 PM

Hi Andrew

Been having Internet Issues all day...Spectrum is on my Pole as we speak...I'm looking at it...

**jaslake** · 08-16-2017, 10:25 PM

Hi Andrew

Internet is recovered...working on it...you don't require the Date?

**zextrot** · 08-17-2017, 09:00 AM

correct. i wanted to narrow it down to absolute required data. the date is stamped on the email they send so we are good.
also not necessary to name the file anything.

**jaslake** · 08-18-2017, 01:46 PM

Hi Andrew

The Code in the attached "appears" to work on all your Data Dump Files. You'll need to keep the Key Words Worksheet updated as necessary. Please note, KeyWords is a Dynamic Named Range and will automatically update as you Add/Delete Key Words in the Key Words Worksheet.

There are two UserForms in the attached...
UserForm1 in which you'll select the Key Word(s) appropriate to the Customer.
UserForm2 in which you'll provide the Row Number that contains the Customer Name and the Row Number(s) of the Purchase Order Information.

Let me know of issues.

Edit...Please Note Key Words are case sensitive...

**zextrot** · 08-21-2017, 08:47 AM

Forgive any ignorance I display here.
If I understand correctly, this is set up so that it has to be run on every PO that comes in.
I'm wanting to use it as a 'code generator' .. so that for customer "X" I can choose what is needed in the userforms, then the customized macro code is generated for that specific customer. Then we just run the macro on that customer every time so we don't have to go thru all the steps each time.

**jaslake** · 08-21-2017, 05:20 PM

Hi Andrew

What are the Constants for the given Customers (A thru Z):

Key Word(s)
PO Line No 1
PO Line No 2 (if any)

**zextrot** · 08-22-2017, 08:50 AM

A given customer .. say woodcorp, or customer "X" .. we would identify that customer's constants. All of Woodcorp's POs would be formatted consistently on the header, but will change on the rows with EA, BX, etc. I believe your codebook achieves that. The userform1 selects the key words appropriate for customer X, userform2 selects the constant rows in their header.

I want to go thru this selection process just like you have it, but instead of running the codebook against every PO that comes in for customer X .. I want my selections to generate a customer X macro code that is a customized macro for customer X. Now when customer X's POs come in, we just run their customized macro so we don't have to go thru the selection process every time.

If the customer were to ever alter their formatting, we would run thru the selection process again to update their macro.

If the customized macros show up as buttons in codebook that would be cool. Otherwise just code that can be cut and pasted into my 'personal' file would be sufficient.

**AliGW** · 08-22-2017, 08:52 AM

This thread is marked as solved - is it?

**zextrot** · 08-22-2017, 09:02 AM

We started talking about making the initial code more dynamic so the conversation continued.

**AliGW** · 08-22-2017, 09:23 AM

Then you might want to remove the solved tag.

**zextrot** · 08-22-2017, 09:26 AM

Yes ma'am, done.

**jaslake** · 08-22-2017, 10:50 AM

Originally Posted by zextrot

A given customer .. say woodcorp, or customer "X" .. we would identify that customer's constants. All of Woodcorp's POs would be formatted consistently on the header, but will change on the rows with EA, BX, etc. I believe your codebook achieves that. The userform1 selects the key words appropriate for customer X, userform2 selects the constant rows in their header.

I want to go thru this selection process just like you have it, but instead of running the codebook against every PO that comes in for customer X .. I want my selections to generate a customer X macro code that is a customized macro for customer X. Now when customer X's POs come in, we just run their customized macro so we don't have to go thru the selection process every time.

If the customer were to ever alter their formatting, we would run thru the selection process again to update their macro.

If the customized macros show up as buttons in codebook that would be cool. Otherwise just code that can be cut and pasted into my 'personal' file would be sufficient.

Hi Andrew...I'm not familiar with the process you describe...sorry...

**zextrot** · 08-22-2017, 11:15 AM

The codebook is essentially generating a macro code. User chooses the constants in the userforms and the resulting customized macro is run on the data set. I just want to be able to capture the resulting code so I don't have to run the codebook against every PO that comes in from customer "X".

I want to run the codebook against customer "X", capture the code.
Then run the codebook against customer "Y", capture the code.
Then run the codebook against customer "Z", capture the code.

The captured codes are specific to each customer and can be used every time for that customer, so I only have to go thru the codebook userforms once per customer.

**jaslake** · 08-22-2017, 05:52 PM

Hi Andrew

I'm not suggesting this can't be done...I'm suggesting I'm not schooled in this...don't know how...

I'd suggest you need to develop a tool like this...which I can work with...
Table.jpg
Beyond that, I don't know how to help.

**zextrot** · 08-23-2017, 09:45 AM

ok .. can you explain columns C & D please?

**jaslake** · 08-23-2017, 11:08 AM

Hi Andrew
See your Post #35 for an explanation of all Columns.

**zextrot** · 08-23-2017, 11:16 AM

ok I see what the columns are referring to now.
are you saying if i provide the info in a table form this is doable?

**jaslake** · 08-23-2017, 11:49 AM

Hi Andrew

With the revised Code, the User will be asked to select a Data Dump File to open (as is currently done). The User will then will be asked to select a Company Name from a List, perhaps a Dropdown. The Code will find, in the table you've provided, the Company Name selected and extract the Key Words and the location of the PO Number information for the selected Company.

That's my current thinking...will this work for you?

**zextrot** · 08-23-2017, 11:54 AM

The problem is having to run the process on every file that comes in. I'm trying to take out the user doing all the steps on every file. Once we determine the parameters of customer X, those parameters will be the same for every file they send. The user opens the file, hits a shortcut key to run the customer X macro and that file is emailed off and the file closed. User is done at that point.

**jaslake** · 08-23-2017, 12:12 PM

Hi Andrew

The user opens the file, hits a shortcut key to run the customer X macro and that file is emailed off and the file closed. User is done at that point.

Then you'll need to tell the Code where to find Customer X Name as the Code has no Clue what the Customer Name is.
I suppose you could have a Button for each and every Customer...is that really what you're looking for?

**jaslake** · 08-23-2017, 12:35 PM

Hi Andrew

Another thought...will your Data Dump File Naming Convention ALWAYS be consistent...Company Name ALWAYS following the dash(-)?

data dump - city corp.xlsx
data dump - denver corp.xlsx
data dump - southern corp.xlsx
data dump - wood corp.xlsx

**zextrot** · 08-23-2017, 12:54 PM

Originally Posted by jaslake

Hi Andrew

Another thought...will your Data Dump File Naming Convention ALWAYS be consistent...Company Name ALWAYS following the dash(-)?

data dump - city corp.xlsx
data dump - denver corp.xlsx
data dump - southern corp.xlsx
data dump - wood corp.xlsx

no, these are just names i came up with during desensitization

**zextrot** · 08-23-2017, 12:56 PM

Originally Posted by jaslake

Hi Andrew

Then you'll need to tell the Code where to find Customer X Name as the Code has no Clue what the Customer Name is.
I suppose you could have a Button for each and every Customer...is that really what you're looking for?

If keeping everything in the codebook framework, then yes I am looking for a button for every customer.
Or if looking at this in the framework of a "code generator," it would produce a text file of macro code that can be pasted into a personal workbook of macros and assigned a shortcut keyboard key and named the customer X macro for example.

**jaslake** · 08-23-2017, 12:58 PM

Well, that begs the question...what does your Data Dump File Naming Convention actually look like...can the Company Name be extracted from the Data Dump File Name...consistently?

**jaslake** · 08-23-2017, 01:01 PM

For me, "code generator" is not an option...

**zextrot** · 08-23-2017, 01:30 PM

Originally Posted by jaslake

Well, that begs the question...what does your Data Dump File Naming Convention actually look like...can the Company Name be extracted from the Data Dump File Name...consistently?

We don't name them; they come in with random filenames. Yes it would be ok to take a value out of the data set and rename the file. I think that's what was happening early on in the conversation and then we dropped it because it was not necessary on our end. I'm curious why you think the filename is a necessary thing?

Separate button in the codebook for each customer would work great. Code generator isn't necessary.

**jaslake** · 08-23-2017, 02:05 PM

Hi Andrew

I'm trying to eliminate the need for a Button for each Customer...limit it to one Button for all Customers.

The File Structure of the Data Dump Files are different across Customers...you've assured me that for a given Customer the File Structure is consistent.

So, we discussed setting up a Reference Table that has the Constants for each given Customer (Posts #59 -> #62). I'm looking for a Hook that will tell the Code which set of Constants to use based on the Customer Name being processed.

I'm flying blind here as I'm trying to write Code based on Files (or File Names) that don't exist in the real world...a separate Button for each Customer may be the only option unless the Code can determine what Customer Name the User has selected to process.

**zextrot** · 08-23-2017, 02:43 PM

Wood Corp will glitch because the header is not at the top where it would be consistent. Let's drop that customer. I've added Gorpey in their place. I've attached here the data dump files to test and the table including a unique identifier for their name.

Need to be able to make updates to the table, like adding another value BX, PAC etc .. or changing the name value to their real name instead of the desensitized version.

**jaslake** · 08-24-2017, 03:03 PM

Hi Andrew

I've attached two Code Book versions. Version 2 is the one I'd suggest as it's easier to maintain...you simply need to keep Sheet Constants updated for each Customer...no Buttons to add.

There are two Dynamic Named Ranges in Version 2. Maintaining Sheet Constants will maintain these Named Ranges.

Let me know of issues.

**zextrot** · 04-03-2018, 02:14 PM

Sorry for the delay in responding to this. Partly it is because our company moves slowly. Additionally, we have been trying to make this work out with our system and our goals/objectives for the macro. Unfortunately it is still requiring too much manual processing. If the company would allow hotkey or something it might be different. As things are, we need to go back to the original request which is to create a macro generator that can produce a packaged macro for each customer which can be embedded into our software so that it will run on that customer's orders.

Do you think we could have more luck getting this done if we post the question in the Commercial Services section?

Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Re: Data validation by row

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Data Validation: How to clear/delete the content of the cell and not Data Validation List?

Bookmarks