Is there any way to script this?

[Kumorigoe]

I have a question. It's a little long-winded, so bear with me.

I work at an auto auction. We have a Ford factory sale every two weeks. After this sale, I am required to take attendance data from our AS400 system and put it into an excel spreadsheet. This can take a while, as I have to import the data as a .txt file, clean up the spreadsheet, perform sorting and subtotal operations, and create totals on each of the five tabs in the spreadsheet.

I know that scripting might not be possible for this, but I thought I would ask. Thanks in advance for any assistance!

 

[SkipVought]

hi,

Hardly ANY wind at all, just a slight breeze! ;-)

If your data in the AS400 system, reside in TABLES, you may be able to access your data directly from Excel via a query.

Nonetheless, first, I would IMPORT the text, using Data > Get External Data > From Text... and use the wizard to drill down to the text file containing your data.

This places a QueryTable on your sheet that can be REFRESHED in order to import new data as it becomes available.

What is involved with "clean up the spreadsheet"?

Depending on the cleanup effort, any repetetive steps can be RECORDED using the Macro Recorder. Ususlly this generated code can be further modified to make the process work better, and it can become a "push the button" task.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Kumorigoe]

Hey, thanks for the quick reply.

The data from the AS400 is an ATTEND report. It changes every time. What I've been doing is printing it to the system printer queue, and then importing that opening that txt file in Excel. From there I have to clean up the data imported, as it's not exactly a CSV. I haven't ever tried importing like you said. It's possible that I won't have to clean up as much using IMPORT as opposed to OPEN.

As far as what happens after the data is in Excel, it involves filling in missing fields, sorting by a column, then copying that data into four of the five tabs. After that, I manipulate the data in each tab differently. One requires me to remove duplicates in one column, one in another. The next two are subtotal operations with different counts on different changes in the data. The fifth tab is filled with data from a different report generated from the AS400.

 

[Kumorigoe]

Attached is an example of the data after import. Same results as I was getting.

 

[SkipVought]

filling in missing fields": is there a crystal ball required?

"sorting by a column": simple macro record process.

"copying that data into four of the five tabs": there may be a better way than copy 'n' paste, as we get a better understanding of your reporting requirements, but probably a simple macro record process.

"remove duplicates": depending on your version of excel, this is a few mouse clicks, simple macro record process.

"subtotals": several built in fearures can produce such values, simple macro record process.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Kumorigoe]

The only problem with a macro recording is that I'm usually deleting rows individually depending on the data. If I can find a way to tell excel to get rid of the data I want gone without screwing it up, it might work.

 

[SkipVought]

HOW do you decide what rows to delete. In other words, what logic do you use to make that decision?

HOW do you decide what data goes into missing fields?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Kumorigoe]

In the PMT column, any blank cells are filled in with FLOOR

As far as removing duplicates, it depends in the tab. In the BODYCOUNT tab, you remove rows that contain duplicates of data in the REP_#1_NAME column. For DEALER BY DEALERSHIP, you remove rows if the DEALER# column matches.

 

[SkipVought]

Well, then THAT is the logic.

Question: "In the PMT column": What other TYPE of data is in this column? TEXT / NUMBERS / DATE / OTHER?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Kumorigoe]

PMT column is just text.

 

[SkipVought]

Here's a tip on filling in the empty cells in the PMT column. Once you get the process down, turn on your macro recorder and record doing it and then see how it works when you RUN the macro.

Here's the process
[tt]
1. enter is formula in column J in row 2
J2: =if(trim(h2)="","FLOOR",h2)

2. COPY column J

3. SELECT H1

4. right-click > Paste Special -- VALUES

5. DELETE column J
[/tt]



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I forgot to include one important step

2.5 copy/paste the formula in J2, thru all rows of data in column J.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Kumorigoe]

Thanks. I'll have to try that out

 

[Kumorigoe]

Well, they're not always FLOOR. Some are CHECK depending on the dealer payment used.

 

[SkipVought]

FLOOR" is only returned when the value in column K is NOTHING. Otherwise the value in column K is returned.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Maybe you need some Excel 101.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Kumorigoe]

I probably do. This is about the only thing I use it for, and it's only twice a month. Maybe I'm just lazy

 

[SkipVought]

Of course, if you are not a regular user, then it unlikely that you will ever develop the depth of skill & knowledge that can make great use of this incredible tool.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[randy700]

"Well, they're not always FLOOR. Some are CHECK depending on the dealer payment used"

So, modify Skip's formula ...
(Assuming the dealer payment is in column A)

J2: =if(trim(h2)="",if(a2="TYPE1","FLOOR","CHECK"),h2)

Randy