Please please help!

[slash32487]

What you are looking at is a excel file i created from the import new data wizard. Now my report has a header which you can see at the top is all cut up because of my columns. Less importantly within the report you can also see "r This Colo r" and "r This Styl e" it is suppose to say Total For This Style/Color. i eliminated columns hence you are missing some of thoes letters and words. Now is there a way to have excel not breakup my header? Once again not nearly as important is it also possible for excel not to breakup the total lines?

 

[c_m_s_jr]

Most likley now. I assume the data you are importing is from some sort of text report. When you import data, Excel makes its best effort to place the data into the columns (since it is a spreadsheet analysis tool). Depending on the file being imported it will use certain characters (commas, etc.) or even just spacing to determine where your data needs to break for various columns.

 

[TomRenish]

Instead of "importing" the file, try opening it instead. Then select only the data you want to split across multiple columns and run the "text to columns" feature.

 

[charliedontsurf]

Is the data comma delimited?
If so, then the headers would also be equally delimited.
Therefore, just be sure that the Comma delimiter is checked (in step 2 of the wizard). Don't let Excel "guess" how to delimit your data for you.

However...
if your data is not comma delimited... then how is it delimited ?
(look at the data file using notepad)

 

[slash32487]

it is tab delimited

 

[slash32487]

text to column is only one column at a time this is a 100 page report. i consolidate it and it takes tons of time so im thats why i need assistance. if i were able to delete all the columns/ rows that have the header in it i would and get rid of there row so it all moves up that would be amazing any help would be amazing i need to run this report often and its very time consuming.

 

[TomRenish]

I'm not sure I understand the concern. If all the data is in one worksheet, highlight everything from A5 (or wherever the structured data starts) to A65535 (or however tall the column is) and do the text-to-column thing.

Done, no?

 

[mikeopolo]

If this report has to be produced frequently, then it would be worth spending some time to parse the actual text file, rather than import it. Can you post a short-ish sample of that for the experts to see?

If it were me, I'd be trying to read the source database directly - is that an option?

Regards
Mike

 

[slash32487]

basically the report is created from a old erp solution the program used to access the software is called tiny term its like a ms dos program its kinda bad but its what my warehouse chooses to use here is a link to the actual file it creates the way i get the file is i ask the program for the inventory in the warehouse and i print to spool then it gets saved on the server and i use core ftp and grab it from the server

http://dc125.4shared.com/download/1...JILIKJMJRIZIRIYKWITIYITGLJCHWGFGBIHIBJUJBKKIX

thats the file please let me know what you can do with it

my objective everytime is to clean it. the report is a warehouse location report so if there is a style in 2 spots it will show it twice so i need to get rid of that and combine i also get rid of all the page headers and last but not lease i get rid of total by color/ by style

 

[mikeopolo]

Thanks for making the file available. I've had a quick look at it. Even without automation, I suspect you could get the clean-up done faster than at present.

My suggestion would be as follows:
- Import the file using a macro (which basically saves the import settings)
- Add column headings manually
- Add a sequence number down the side (optional but helps revert to the original row order if necessary)
- sort on the second column, which contains "IM" for every data row
- delete every row that doesn't have "IM" in it. (These will now be at the top and bottom of the worksheet)
- prepare a pivot table to summarise by style. The settings look like this:
Row: Style number, description (no totals)
Column: (none)
Data: Count of Style, Sum of Cartons, Sum of Total Quantity (or as you require)

This process does not take long. I'll get back to you with a suggestion for the import macro.

Regards
Mike