Formating data to view better

[vijanand1279]

Hi,

On the attached spreadsheet I need help to format the data to look better. Since the data is in tabular format I am finding so much time to format manually on the Parked report tab. I have also added sample Format tab with the column header and the below sample data it should appear like.

Please help with macro or functions.

Regards,
Vijay

[vijanand1279]

Could you help me on this.

[jaslake]

Hi Vijay

The code in the attached works on your sample file...test it on a COPY of your live file. Let me know of issues.

[vijanand1279]

Hi John

The attached file works fine but when I am trying to copy the data from the attached text file and paste in A1 to run the macro it shows me an error.

could you please why this is hapenning.

[jaslake]

Hi Vijay

The structure of the pasted data is different from the structure of your original sample file, therefore the macro fails. Is your newest .txt file what the structure will ALWAYS look like?

[vijanand1279]

Hi John

Yes this is how the structure will look always.

REgards,
Vijay

[jaslake]

Hi Vijay

This is a major rewrite to clean up the Raw Data so it can be parsed. I'm traveling from this afternoon until Sunday evening. I'll get back to you.

[watersev]

hi vijanand1279, though it's pretty ungrateful to deal with such sort of tasks I would give it a try, please check attachment, press "Get data" button on Sample Format sheet and select txt file to process.
The code has been polished to process data in the provided txt file correctly though there might be errors on other nearly similar files with different layout

[vijanand1279]

Hi John


Could you help me on this .

[FDibbins]

Hi Vijay

This is a major rewrite to clean up the Raw Data so it can be parsed. I'm traveling from this afternoon until Sunday evening. I'll get back to you.

member said they will be back sunday evening

[jaslake]

Hi Vijay

Have you tested watersev's code? I ran it on your new book and it appears to perform flawlessly.

[vijanand1279]

Hi Watersev,

Yes this is really awesome...I tried to run the report and it worked perfect. Thank you so much.

I was trying this to create a macro by recording the steps in excel. But it never worked for me. I think I have to learn other ways to create a macro instead of recording all time.

Thank you....

[vijanand1279]

Hi John,

Yes it is working fine. I tried to run it in with multiple reports and it works fine.

[vijanand1279]

Hi Watersev,

I tried to run the macro with the new report. But I am seeing some missing items from the original text file attached....I have saved the missing items in the seperate text file attached. Also the first row with 380.41- is actually a credit but it is showing as 380.41 after executing the macro. So any credit entry on the text report is showing as positive (Debit) amount.

Please help on the above bug.

Regards,
Vijay

[vijanand1279]

Hi Watersev,

Yes this is really awesome...I tried to run the report and it worked perfect. Thank you so much.

I was trying this to create a macro by recording the steps in excel. But it never worked for me. I think I have to learn other ways to create a macro instead of recording all time.

Thank you....

Help me with the bug....

[vijanand1279]

Hi

Could you check on the above bug and help me. Not sure why some of the items are missing from the original and also the credit amount shows in positive.

Regrds,
Vijay

[watersev]

please check attachment, the code was updated to clear any data prior to new output

[vijanand1279]

It looks to be working fine now. I see the credit amount also picking up as it is. But the total doesn't match when I do a sum for all the rows. When I change the minus symbol to prefix for the entire credit amount the grand total amount matches with the original total amount...

Is it possible to see the invoice amount contains hyphen (Credit) and have the hyphen in the front of the invoice amount?

[watersev]

please check attachment

[vijanand1279]

This is working fine. I am sorry for late response. Because I had to wait till today to check on new report. This is real time saver.

[vijanand1279]

Hi Watersev,

I am getting "subscript out of range" errro.

Run-Time error '9':

Subscript out of range.

Please help. I am attaching the spreadsheet and the text file.

Regards,
Vijay

[vijanand1279]

Could you help me on this debug.

[watersev]

please check attachment, txts are the files it was tested on

[vijanand1279]

It looks fine now. Why was it throwing subscript out of range error. Is there a method that we could prevent from such type debug. I am not sure what is causing an error.

Please help me to understand better.

Regards,
Vijay

[watersev]

you post sample file that does not represent all possible data patterns. In this particular case the problem was non-standard opening line:

Standard in all previous sample files:
SVCS EXECUTIVES CLUB OF CHICAGO 2059 1105

Non-standard causing error:
SVCS FRONTIER

I can not guess what it might be the other time. So the root is data consistency or forsee all possible data patterns. The other way is to ignore any error though that might be a bumpy way as reports will have errors you do not know of.

[vijanand1279]

Thank you. I also noticed the difference in the line opening the report. Hope I don't get any different format reports.

This is really working fine and a time-saver too.