Sort in Column D and Column H and copy Sheet3

[abjac]

HI I have a very important code i need to solve, so hope for some help here.
I have a sheet with many informations.
What i special need to copy and sort is where there in Column D is more than one customer with the same customer number.
And in this more than one row with the same customer number, have from Column H
The word Invoice, and one of these words Credit note or Payment.
and then copy this to Sheet 3.
If i can have this, i can in Sheet3 try see if some of the invoices can be balanced with the creditnote or payment.

I have attached a sample sheet below.
The customer number is in column D and it always look like this
00001634 -R TE
Where the first number is the customer number 8 digits with zero infront. The text behind i cant remove, its imported that way
The text in column H is more regular. Creditnote or Invoice or Payment.

Please have a look

Sincerely

Abjac

[oeldere]

See the solution with an pivot table.

[abjac]

Hi Oeldere. I have been trying to use this solution, and think its quite difficult. For sure because i have never used it before. Is it possible for you to record a macro, of the pivot table you send to me. Its the one i need, just that it have a range to the end.

I think i need this or a reall macro to do it. I am sure its smart with the pivot table.I have just never worked with it before.

Please have a look and see if you have time to record a macro with the pivot.

Thanks

Sincerely

Abjac

[FDibbins]

oeldere if offline right now. why do you feel you need a macro for the PT he offered?

[abjac]

Hi FDibbins.
I have never used PT before, and the macro i all ready have, is a step by step one. all with macros. I open a big csv file with macro. I run some macros to example sort the right landcode out, some for delete example settled items, and some for ascending. All quite fast and its working good.
I end with a file which i send in my sample sheet,. But much bigger off course, much more rows.
The Pt Oeldere offered looks really good, but i tried it and i think its difficicult to set up. I cant copy his its readable only( I also use excel 2003, so i need to set it up my self. And that gave me problems, to make it like he did. I know if i use long time to learn to use this, i will be able to do it. But i think this will take me quite some time. The other is that the macro i make with the step by step buttons, shall be ok to use for my partner also. And she dont know so much about excel. So it need to be simple. So i also in this case think its the best solution, that i can have one button more to make this PT. And then she and me too can save the sheet.
So thats the main reason. I would stil like if some also could provide me with the VBA code to do this.
I would like some VBA, which can read column D. And collect the one with the same customer number in Column D, which have an invoice, with either a Creditnote or Payment from Column H.
And then delete the ones, which dont have this combination.
This it the optimal for sure.
But Oelderes PT is really great for sure.

Hope my explanation make sense smile

Have a look
Sincerely
Abjac

[FDibbins]

ok i understand you're reasoning. however, once the PT is set up, you dont need to set it up again - ever. at most, all you need to do is refresh it when new data is added, and it does its thing without any help from any1.

I also understand that you "currently" lack skill in setting up a PT, but trust me, once you have set this 1 up, you will be wanting to use them all the time, they are really powerful.

I will let oeldere help you set it up and walk you through the process though, he is really good with PT's

[Winon]

Hello abjac,

See if the attached WorkBook is what you want.:)

[abjac]

Hi FDibbons. Thats sound good. I like you said have also a feeling when i learn and get use to us PT, it will be great. I can see the potentials in this solution for sure. So lets hope time will learn me that. But it will be great if Oeldere can help with this solution. Its the last thing i need to make this quite complycated step by step macro to work so would be great if you can ask him to help.
Hi Winon and thanks for your answer too. I as i said, would like to have and try a solution with VBA , your code look interesting, but it come up with an error, when i try to run it with the button in this line " ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear"
But when i look at the code, it could seems that it also can work. So if you can correct the error, i would really like to try this one.

Thanks to both for the answer so far. really great forum this one. I am learning every day, so maybe also the PT i can learn.

Sincerely
Abjac

[Winon]

Hello abjac,

Just remove that line

Please Login or Register  to view this content.

[oeldere]

@FDibbins

Please Login or Register  to view this content.

Thank you very much for your compliment.

@abjac

I tried the solution from Winon and the code works fine (with excel 2007).

@abjac
I would like to learn you pivot table, however

1) You use Excel 2003 and I use Excel 2007 => there is a differance in building a pivot table in those versions

2) You have probably an spanish excel version, i have an dutch version

But the most important reason

3) You learn more of the skills of Pivot table if you try it yourself (instead of getting an working solution from the forum)

I bet there are examples on you tube to show you how to make an pivot table.


So I suggest you learn the skills of pivot table yourself, and when you get stuck, post an example of your data and an question where you get stuck.

If you have comments or questions on this item just ask or comment

About the macro part:
I'm realy new in the VBA world and my skills are probably not good enough to get an working solution for you.
I would make it by recording it with the macro recorder, but that won't be good enough for your problem (I suppose).

[abjac]

Hi Oeldere and Winon.

Winon i tried to remove the line and it still come up with an error. But i really would like this one to work. Seems very interesting. I use excel 2003 english version. So if you can make this one work in excel 2003, it would really be great.

Hi Oeldere. Yes i understand what you mean.And your are correct. I use upper excel version and that is maybe the reason why i cant set it up like you. If you have time to record the PT, it would be great, and send me the recorded macro I could try it maybe it will work correctly in my excel version. Would like to try that.
But otherwise. i will thank you great with so good help here.

Have a look both if you have time.

Sincerely
Abjac

[Winon]

Hello abjac,

Then instead of removing that line, insert this just above that line:

Please Login or Register  to view this content.

[oeldere]

below an link which i found with google for pivot table 2003

http://www.google.nl/search?q=you+tu...M6WX0QWkl4HgAg

Please respond if this was usefull to learn your skills.

If you can make an pivot tabel with that, you can record the code with the macro recorder.

[abjac]

Hi Winon. I made your code work now. It dont fullfil totally what i wanted but its working now. So thank you very much for your help.

Thanks for the link Oeldere. I will have a look and later on let you know if i have learned something haha. I think i will.

Thanks to both of you, nice with good help here

Sincerely
Abjac

[abjac]

Hi Oeldere.
I have worked with the PT all day here, and i make it work now. Saw the link you send to me and they gave a good explanation how to start. So i make a good layout . And i managed to implant this Pivot table in may quite advanced macro step by step . So now you can deside it you want to use the PT in the end. Its all working now so thank you very much for your help. I am infact sure about later i will love this PT, its really great and have alot of possibilities. So again great board and alot of good help. Thanks alot Oeldere and all other.
Sincerely

Abjac

[Winon]

Hello abjac,

You are welcome.

Glad we could help.

Thank you for the Rep.

[oeldere]

Glad I could help.

Thanks for the reply.

I'm glad you tried to work with PT yourself.

If your question is solved, will you mark it as solved?