multiple excel files joined to one,and cleaning up stuff

Hi,

Welcome to the forum

I assume the attached file is the list into which the data must go that comes from the other excel files.
How do the other excel files (the source files) look like? Are they also lists with the same data structure as your attached file? in other words, can a macro simply open the multiple source files, copy the list from that file and paste it into this destination file, or is there other actions that I am overlooking?

If possible, can up upload a copy of a source file (desensitized) so we can see what it looks like?

TX

I think that RudiS asked for: Is the structure of the other workbooks the same.

So are the information of the zip code always in the same column.

Hi,

Here is a workbook containing a macro that should do the job. Just open the template and click the button on the first sheet.

Note:
Ensure all the source workbooks that you want to combine are in a single folder so you can select them all when the macro prompts.
After you select the source files and choose Open, the macro will do the rest and combine them all onto the AllData sheet.

Hi,

You can try this code. Replace the previous macro with this one.

It will delete the zip codes < 18000 (assuming they ARE numerical)
And sort the zip codes (if that is still needed).

Please Login or Register  to view this content.

As I mentioned.... I am assuming the zip codes are numerical (not "text" numbers or mixes or number and alpha characters, but actual calculative numbers).
Can you confirm that?

If it is possible maybe you can upload a sample of data (desensitized), but leave the zip codes in place...then I can try resolve it based on this data.

Hi,

Here is the updated template that should remove all Consignee Zip Codes less that 18000.
I have not added any code to delete unnecessary columns as I do not know what to remove, although that is easy: You can just type:
Columns("B").Delete, etc...

Ah....my fault, and a silly one at that!
I forgot to set a reference to the AllData sheet, so the macro was running the filter on the incorrect sheet.

Try now. I tested on my side and it ran smoothly now.

Attached...

Excellent...glad to hear it is good now

Good luck on those additions.
If you need assistance, just continue asking in this thread...
Cheers

Hi,

I commend you for trying, but I am here to assist

Based on info you have provided in previous posts in this thread I have added these actions to the macro.
Attached is the updated template containing the new macro.

It will remove the TK and GR letters before the Zip codes,
It will delete those extra codes you referenced,
It will remove the Zip, Fax and Email columns (please check if I delete the correct ones. You can slot in more in the macro too...just ensure the columns listed are listed in the macro in a descending order so it deletes from the right to the left to avoid columns shifting and wrong ones being deleted. See the note in the macro as I wrote it.

I trust this will help...

Hi

Attached is the updated workbook that includes deleting all the indicated columns.

goooood morning!!! i'm speachless!!! THANK YOU!!!!

one last (i hope...) question...
lets say,i record one new macro inside the file you gave me,to add some more functionallity,why it does not work?
to give an example...i run the original macro,sets the final data in AllData sheet,all ok.
then,i go to this sheet,and i click "record macro" to wrap the text of all cells,but when i reopen the file,it simply does not work...
how can i add some more functions by editing the existing code? i tried to copy-paste the recorded macro,but no luck!

(maybe,this one should be posted as a new thread...)

anyway,i dont want to be too demanding! you have done a lot for me so far.....greetings from Athens,Greece!

Good morning to you too... LOL
Glad it is working.

Have a great day!

Hi Athlug,

Greetings from a cold and wet Cape Town too

You are able to record additional macros into this file. Any macros you record will be added to a new module in the VBA project. Those macros will definitely be able to run and do other automated tasks you need. To answer your question regarding merging them into the current macro so that the main macro will also run the new ones you create...see the image: 1.jpg

No matter how many new macros you create, you can run them individually by pressing LT+F8, selecting the macro and choosing RUN (from the dialog), or you can integrate them into the master macro by adding a line: Call <MacroName>. This means the master macro will jump out of its routine, find and run the *called* macro, and then continue the master macro after the called macro has run...

Hope this answers your questions.
See a sample of what I explained in the workbook attached...

Hi,

Just one technicality why the code was not running.
Remember that you are running the macro from the Macro Sheet, so it is important to build into the macro that the wrapping and sorting must be applied to the "AllData" sheet. In your macro you just had Range("A1:H123").Font...... This ends up applying the command to the Macro sheet (sheet 1). In staed, you should have the code as follows: Worksheets("AllData").Range("A1:H123").Font...... Now when you run it from the sheet 1, it still runs the commands on the All Data sheet.

My code below connects all the actions to the AllData sheet using the With statement.

I have fixed it in your workbook and attached the working sample...

Please Login or Register  to view this content.

good morning all! back again,with some ideas to improove the macro...so,we 'd like to achieve the following:

1. DELETE zip codes 00 TO 180 00 AND 00 TO 18000 (we got this one already...)

2. WEIGHT UP TO 2.2 KILO TO BE RENAMED TO 2

3. SHORT BY WEIGHT

4. MARK SOME ROWS WITH SPECIFIC ZIP CODES WITH A COLOR (lets say,we want to mark all rows which have zip code 74000 and 71000 with a green color,to find them easier..)

Dear RudiS,got some ideas maybe? me, i'm searching the web for some info,to do it by myself,but i also kindly ask for your support!
greetings from rainy athens ! (gee,rain in June....i hate it!)

Hi Athlug,

It's raining here in Cape Town too...but I cannot protest because it is winter LOL!!

I'll look into this request this evening when I get home...

Hi,

1. DELETE zip codes 00 TO 180 00 AND 00 TO 18000 (we got this one already...)
Do you mean to say this question is resolved, I can ignore this request?

2. WEIGHT UP TO 2.2 KILO TO BE RENAMED TO 2
To clarify... Do you want all weights >2 and <2.3 to be re-entered as 2 (without the decimal value)

3. SHORT BY WEIGHT
I will sort by weights

4. MARK SOME ROWS WITH SPECIFIC ZIP CODES WITH A COLOR (lets say,we want to mark all rows which have zip code 74000 and 71000 with a green color,to find them easier..)
I understand this one too

Please reply with answers to the questions above, then I will work on the macro...

hello hello! sorry for being unclear..well,untill now,we can delete the rows from 00 to 18000.that's what i meant we got this one already...so,we can keep this function as is (i think)
now,we need to delete also the rows with zip code like 00 00 up to 180 00 (in this form,with a space between)

in part 2. To clarify... Do you want all weights >2 and <2.3 to be re-entered as 2 (without the decimal value),yes that's right....we need to round the heavier that 2 kilo and up to 2.2 kilo to be only 2 (if possible)
and in 3,if we can mark them (or even better to move them into a new workbook....),so we can easily find them and move them into some other book.

thanks for your support!

oups...double post by mistake.....

Here is a workbook that contains the updates.
You can tweak the code if you need, like update the ZIP codes that need to be highlighted in green.
All the other requests are done.

Check if all is accurate as you need it.
TX

good morning my friend! just tried the new file,and here is the feedback...
got this error 1004, autofilter method of range class failed,and the in the debug i see this line marked yellow:
Range("A1").CurrentRegion.AutoFilter Field:=8, Criteria1:=">=2", Operator:=xlAnd, Criteria2:="<=2.2"

Morning...

The code does not produce an error with my sample file. I am still using the London sample file that you uploaded in this thread.
If it continues to cause errors can you upload a sample of the current file you are importing. Maybe there is an issue with the content that the macro is trying to filter? I can only verify with data that is causing the error.

Summary_LON_0064-13497730.xls

i'm uploading a fresh sample (today's file)...
tried the new file with the old samples too,still this error.....could you take a quick look at it?
thank you!!

Hi,

Sorry that was my fault. I forgot to add a reference to the AllData sheet with the new code I inserted...
It should work fine now...

well...downloaded the last version,but now gives me "no file selected" no matter what!!! i used notepad++ to compare the last working file with the very new one,i see some additions,but in the beggining of the file,i couldn't find something wrong....what did i miss?

When you run the code, you need to select the file that contain the info to compile. The code will process the data into the master file from these selected workbooks.

hm... i still dont get it.....the previous file you gave me,i just have to click and select the files to proceed.
now,when i click the button,i select the files,and then it say "no file selected"...BUT when i switch to AllData sheet and hit run macro,it works....
so i have to go to alldata sheet and run it,instead of the "macro" sheet by using the button....
could you please take a look?

OK...
I made some big changes.
All you need to do is select the source file(s) to import by clicking the macro button.
Each time you run the macro it will clear the template and the AllData sheet and process the new import files into a new AllData sheet.

Test it now...

dear friend....seems something is going wrong....still not working,but the good news is that i managed to run a previous version with some changes that you made,in the latest!
i dont wanna bother you with my problem,you have already done a lot for me....
so,i think we should stick to the latest working version (thats in page 2,the last update,i think....) and i'll give it one more try....
if you please,you could check again the last file,i have a video of the whole thing,i'll upload it if you need it....

well,i'd like to thank you once again,for all the hard work you done for me! we'll keep in touch,for further feedback....

keep smiling!!!

Hi Athlug,

I'm always smiling TX. I enjoy helping and I don't get tired of it.
I am a little "frustrated" with this scenario? The latest macro I uploaded is working 100% on my side with the two sample files you provided. I cannot understand why it is not running on your side. As a matter of fact, this latest one is more accurate and less prone to errors than the old one you refer to. The changes I made should ensure that imports run smoother and the multiple files are appended below each other more correctly. It is strange that you get errors????

I'd be happy to look into things further but I do not know where to start as I don't know what issues you are having?

good afternoon! newsflash:
i finally managed to make it workd,by copying some of the code to another sheet,and recreating the button.
so,i was trying to do this: color the rows that contain specific zip code values (lets say 70000, 70100 and 70200)
you gave me a part of code that does color the cell (not the entire row,but its ok for now...),so i copied it 3 times,changed the numbers but it only applies to the last line...
the code is here:
Sub ConditionalFormat()
Worksheets("AllData").Activate
Range("A1").CurrentRegion.Columns("D").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="70007", TextOperator:=xlContains
Selection.FormatConditions.Add Type:=xlTextString, String:="70100 ", TextOperator:=xlContains >>>>>>> i added this
Selection.FormatConditions.Add Type:=xlTextString, String:="70200 ", TextOperator:=xlContains >>>>>> and this line
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65280
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("A1").Select
End Sub

i also tried to give the strings one by one,but crashes at once...
maybe you got some ideas? or even better,is there a way to move the rows that we select to a brand new workbook?
remember,we need specific zip code values (not in range....)

salutations,Henry

Hi Henry,

Here is the macro that runs perfectly on my side.
It includes the code that moves the ZIP code records to a new sheet in the same workbook.
Just change the zip code numbers in the macro to the ones you need moved.

Please Login or Register  to view this content.

I also attach two images that show what the macro prompts for and what it looks like when finished running.
Prompt for the source data: 1.jpg
After it is run...the end result: 2.jpg

good morning...so far,we're ok,even if i cannot work with this file,i manage to move the code to the one i created,so it works...

YEEEES!!! I did it....so please,dont mind of the post....going one step further,to move the Creta sheet to a new workbook.....
have a great day!!

latest update: i managed to add a new sheet,named Creta,and moved the needed data (zip codes like 700xx) to that sheet.
BUT,everytime i run the macro,it creates one more sheet (sheet3,sheet4 etc) by mistake,so then the macro can not run.....
how to modify the following code,to create only one sheet,named Creta and move the known data to it?

i attach the code below.

Sub MoveToNewSheet()
Dim sh As String
Worksheets.Add After:=Worksheets("AllData")
' WE CREATE A SHEET NAMED CRETA,TO MOVE THE 700xx ZipCode THERE....
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Creta"
sh = ActiveSheet.Name
Worksheets("AllData").Range("A1").CurrentRegion.AutoFilter Field:=4, _
Criteria1:=Array("70007", "72200", "72300", "71306", "71409", "73100", "73131", "73300"), Operator:=xlFilterValues
Worksheets("AllData").Range("A1").CurrentRegion.Copy
Sheets(sh).Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
Sheets(sh).Range("A1").PasteSpecial
Application.CutCopyMode = False
Worksheets("AllData").Range("A1").CurrentRegion.Offset(1).EntireRow.Delete Shift:=xlUp
Worksheets("AllData").AutoFilterMode = False
Range("A1").Select
End Sub

Hi, and morning to you too

Here is code to move to a CRETA worksheet or to a new workbook...

Please Login or Register  to view this content.

good morning again! seems it'll take long to finish this project....
could you give me some directions to set a range,that will select all rows from weight 0 up to 2 kilos and move them in a new workbook?
you see,what i do every day is this: check the shipment weights,split them into 3 files (0 to 2 kilo), >2 up to ....whatever and the last,that is the 700xx zip codes,that goes to a different workbook.
than i import them into a new program to get printed labels...

thanks in advice!!

double post again...server stucks sometimes...sorry!

Dear Rudi,once again,i ask for your help....take a look, is it possible to clear the contents of this file?
in my sample xls,there are some contents (text) that i don't need.
i'd like to remove the duplicate entries from the Consignee Address rows based on the contents of the Consignee City cells.
what i mean? lets say, here,the word CHIOS shows 3 times,we dont need that,so we have to delete it in the Consignee Address,and leave it only in the Consignee City

Shipment Number Consignee Consignee Address Consignee ZipCode Consignee City Consignee Phone Pieces Shipment Total Weight
0866-03636276 MRS NELLH NIRI LEAFOROS AIGAIIOU 22 CHIOS CHIOS 82100 CHIOS 302023288 1 0.11


so,the final text should be like this:
Shipment Number Consignee Consignee Address Consignee ZipCode Consignee City Consignee Phone Pieces Shipment Total Weight
0866-03636276 MRS NELLH NIRI LEAFOROS AIGAIIOU 22 82100 CHIOS 302023288 1 0.11

in the second case,we have something like this:
Shipment Number Consignee Consignee Address Consignee ZipCode Consignee City Consignee Phone Pieces Shipment Total Weight
0866-03636453 MRS KRISTINA L. KALIVION 252 LAMIA -- SELECT STATE -- GR 35100 -- SELECT STATE -- 6954689 1 0.43

here,we want to move the city (LAMIA) ,and replace the -- SELECT STATE -- in the to Consignee City

please check the demo file,to see exactly what we need...

Originally Posted by athlug

good morning again! seems it'll take long to finish this project....
could you give me some directions to set a range,that will select all rows from weight 0 up to 2 kilos and move them in a new workbook?
you see,what i do every day is this: check the shipment weights,split them into 3 files (0 to 2 kilo), >2 up to ....whatever and the last,that is the 700xx zip codes,that goes to a different workbook.
than i import them into a new program to get printed labels...

thanks in advice!!

Hi and good morning,

Here is the code that moves data to a new workbook
Please note that I have not tested it physically, so I need you to give it a try and see if it runs OK.

Please Login or Register  to view this content.

Originally Posted by athlug

Dear Rudi,once again,i ask for your help....take a look, is it possible to clear the contents of this file?
in my sample xls,there are some contents (text) that i don't need.
i'd like to remove the duplicate entries from the Consignee Address rows based on the contents of the Consignee City cells.
what i mean? lets say, here,the word CHIOS shows 3 times,we dont need that,so we have to delete it in the Consignee Address,and leave it only in the Consignee City

Shipment Number Consignee Consignee Address Consignee ZipCode Consignee City Consignee Phone Pieces Shipment Total Weight
0866-03636276 MRS NELLH NIRI LEAFOROS AIGAIIOU 22 CHIOS CHIOS 82100 CHIOS 302023288 1 0.11


so,the final text should be like this:
Shipment Number Consignee Consignee Address Consignee ZipCode Consignee City Consignee Phone Pieces Shipment Total Weight
0866-03636276 MRS NELLH NIRI LEAFOROS AIGAIIOU 22 82100 CHIOS 302023288 1 0.11

in the second case,we have something like this:
Shipment Number Consignee Consignee Address Consignee ZipCode Consignee City Consignee Phone Pieces Shipment Total Weight
0866-03636453 MRS KRISTINA L. KALIVION 252 LAMIA -- SELECT STATE -- GR 35100 -- SELECT STATE -- 6954689 1 0.43

here,we want to move the city (LAMIA) ,and replace the -- SELECT STATE -- in the to Consignee City

please check the demo file,to see exactly what we need...

Hi Athlug,

This is a very difficult request.

Usually in order to code something one needs a bit of consistent logic.
The only logic I can see here is the some of the city names are directly behind the letters GR
We can even use logic to test if two words after GR are the same, then delete one
BUT, this is not always the case that the city is directly behind GR.
As humans we can reason something, but a computer cannot....so how do we test for city names that fall outside the boundaries of logic???

Could I ask a favour. Please create a brand new post out of this request and see if there is not a moderator or more experienced person who can answer it. At best you will get more assistance with a new post or multiple pieces of advice that might guide you to a good answer instead of just relying on my reasonable knowledge

Lets see what comes from the rest of the knowledge base within this forum as assistance.

TX.

hello,again! thanks for your reply...one final question...
how can we split the final .xls (i mean,after clearing up,and moving the desired rows to creta.xls) into two separate files?
what i need to achive is this: select all rows from 0.1 Kg up to the last 2 KG and send them in a new workbook,named lets say "small".
the rest rows,move into another file....
i'll try tonight to modify your code,to see if i can do it....

greetings!

The code above does move the filtered data to new workbooks. It just does not save it (though we can change that to do so!)

Notice, this one moves 0 to 2 weights to a new workbook
The other filters do the same...
If you need it integrated into the main macro, just add the macro name into the Main procedure in the area I indicated.

Please Login or Register  to view this content.

Jesus...i'm a stupid.....i made a big mistake,in my previous post.....i mixed things up!
let me explain....
we need to split the data like this:
a) all rows that goes to crete (you know,the creta ones...) to get into "creta.xls" ( already done) BEFORE separating the weight (up to 2 kg and more than 2 kg)
b) the rest rows now,we have to split them by weight into 2 xls files like small.xls (the up to 2 kilo) and big.xls (over 2 kilo)
step b) must be done AFTER we split the creta files......
i think that's easy,since we already get our creta in another file....

could you please help me clean up the code,because i messed up with the two past posts of you ......
i'll give it a try,using the code you already gave me....hope it will work!

greetings!

Sure...
I'll help set it up.

What about the zip codes that start with 700xx ?
Must that also be saved or scrapped?

Here is the updated file...

dear friend,good morning...latest news...the new file gives me an error 1004 application-defined or object-defined error....
the debug shows this:
.Range("A1").CurrentRegion.Offset(1, .Range("A1").CurrentRegion.Columns.Count - 1).Resize(.Range("A1").CurrentRegion.Rows.Count - 1, 1).Value = 2

now,to be more clear,here are the steps as i think they should be:
1. clear the unuseful zip codes (ok)
2. round the weight up to 2.2 Kg to be 2 Kg (also ok)
3. move all the rows that have zip codes of Crete (the 700xx) to creta.xls (we got it)
4.the rest rows are all other towns,and weights from 0.01 to whatever.these must be separated by weight to two xls files like light and heavy (or whatever,1 and 2, small and big no problem)

i attach a zip file with the files that i use right now...
if you run it you'll end with 2 files: a file name creta.xls and the version 0.5.xls that contains the rest of the data (remember to save this file...)

so,we're very close to the end,it remains to split the data in file version 0.5 into the two files i mentioned....
could you please give it a try? (i do my own tests here,but still i dont got it..)

greetings!

Try this....

Good afternoon my friend RudiS ! i 'd like to say a big THANKS for your perfect work!!! thats exactly what i need!
i made some more additions in the cleanup process (with the zip codes),so i think now it's time to finish it by myself...
you have done a great work here!
one last question about this macro: is there a way to do this:
in file creta.xls,to ADD the new data below the last saved row? what i mean? lets say,we run the macro on on sunday,creates some rows in creta.on monday,we run again,and the new data appends to this file,so we do not loose the older,but we keep a record of all...
thanks again,for spending so much time to help....greetings!

Greetings Uthlag

Glad this project is working well for you.
Here is the attachment containing the changes you requested. It will append the data to the "Creta" file if it can find it existing in the directory in which this attached template is stored. If it cannot find the "Creta" file, it will create a new one. So make sure the Creta file is placed in the same directory as the Macro Template (unless you modify the path to a fixed location.)

heeelloooo again! hope you're fine.....
glad to say i'm done (well,almost,i'm still making improvements...)!
well,one more simple question....how can i auto add the date everytime we add rows in "creta" file?
i want to keep track,so i need to add the date everytime i add some new rows....
(i'm working on it,but as always,i'm stuck...)

Heeeellooooo too

Where do you want the date entered?
-- A single date in a cell at the top of the sheet that gets overwritten to the latest date each time the macro runs? OR,
-- Dates in a column next to each imported record based on when the records were added?

TX

well,it would be very helpful if you could make it look like this (used bold to make it clear):

15/6/2014
0866-03802515 MR ROBERT DAVIES
0866-03811770 MS EVAGELIA PIAGALAKI
0866-03801616 MRS DENISA LAGINOVA
16/6/2014
0866-03790884 MISS MARIA FRAGOULI
0866-03809272 MS EVAGELIA PIAGALAKI
0866-03795294 MRS ANNA VOULGARIDOU
17/6/2014
0866-03790884 MISS MARIA FRAGOULI
0866-03809272 MS EVAGELIA PIAGALAKI
0866-03795294 MRS ANNA VOULGARIDOU

thanks in advice....

Hmmmmm...maybe I should NOT have asked

I'd rather recommend...

15/6/2014 0866-03802515 MR ROBERT DAVIES
15/6/2014 0866-03811770 MS EVAGELIA PIAGALAKI
15/6/2014 0866-03801616 MRS DENISA LAGINOVA
16/6/2014 0866-03790884 MISS MARIA FRAGOULI
16/6/2014 0866-03809272 MS EVAGELIA PIAGALAKI
16/6/2014 0866-03795294 MRS ANNA VOULGARIDOU
17/6/2014 0866-03790884 MISS MARIA FRAGOULI
17/6/2014 0866-03809272 MS EVAGELIA PIAGALAKI
17/6/2014 0866-03795294 MRS ANNA VOULGARIDOU

Reason: The structure will allow for further analysis with sorting, filtering, Pivot Tables, etc...
If you need a structure similar to the one you show, then a Pivot can set that up very easily. ALWAYS keep your source data in a raw table format.

Can I set it up as I recommend or do you still want it in the format you showed...

well,at the moment,w dont care much about the structure...you see,i just keep it for my records (in case we need some reference in the future...)
after a second thought,the way you saw me looks even better...
so,anyway you like,and it's easier (and preferable as you suggest...) i'll say no word!

you have done far too much for us!

I will look into doing the updates the moment I get a chance...

Hi,

Attached is the updated version that includes a date column.

hey! you're great! may i ask a favor? i only need it in the "creta" file,not the other two... (i tried to fix it,but still trying!! copying and pasting the code around,untill i get it...ha!)
if you got a minute,it would be great! thanks anyway,you're so kind.....i feel sometimes that i am too demanding.....

No hassles...that what these forums are for...asking questions getting solutions...etc.
There is no rule on how may questions you may ask

i'll see what I can do...

Try this version which puts dates only in the Creta file.

hi hi hi!!! first of all,THANKS!!! you do a great work!!! works like a charm!
please tell me,is there a way to repeat a part of the code (the one that replaces the unuseful stuff in address - see below...) ?
i need some more lines to add,but they are too many to process by the script-says procedure it's too large.
i added abou 900 lines,and i need to add some 200-so....
i copy-paste the original code,but it crashes....
got any ideas?
thanks a lot,for your attention and support!

here is a sample of the code:

Please Login or Register  to view this content.

Hi,

Great to know its doing it's job

VBA has a limit of 64kb per procedure.
You fix it by splitting that procedure up into multiple procedures that can then be called by the one procedure.

So instead of having:

Sub HugeMacro()
... ' lots and lots of code
End Sub

You'd have something like:

Sub HugeMacro()
... ' a little bit of common code
Call Proc1
Call Proc2
Call Proc3
End Sub

Sub Proc1()
... ' quite a bit of code
End Sub

Sub Proc2()
... ' quite a bit of code
End Sub

Sub Proc3()
... ' quite a bit of code
End Sub

See this page to confirm... http://msdn.microsoft.com/en-us/library/Aa264541

good afternoon! well,it's not so clear to me,unfortunately..........
so,i attach the whole code (in a simple text file) with some extra comments,to see how many lines and what i need to repeat...i have no idea if i can use the same stuff as a new macro (process),but i'll give it a shot tonight,just to try....
i'm not sure about it,because this part of code is part of the main macro,not some subroutine....
i had to remove some lines at the end of the file,because it is over 100 kb !!!
thanks again!

Hi,

Please note I have not tested it in the template.
Please test and see if it runs without errors...

TX

Dear friend....i'm speachless......you did a great work......well,i think it's enough.....
time to close this case,now,i'll try some extra functions based on your hard work....
i'll mark it SOLVED,and in case i need something else,i'll pm you....(if you please,of course!)
last word? a THANK YOU!!!
take care,your friend henry.

Cheers Henry,
It was quite a journey and I'm glad it was a successful one and that i could lend a hand

See you around.
Cheers!!

hi again....still here...with a fresh idea,just for fun (that's only for fun...we do not really need this function!)
is there a way to add a progress bar to show the status?
i managed to add one,but it does work as a separate process,can't figure it out,how to assign the parts of the code to the bar...
if you could assist,it would be nice,but as i said,we can live without this!
greetings!!

Hi,

I try and avoid using those bloated progress bar dialogs as they slow down the code and simply cause too many hassles (IMHO)
I tend to prefer a simple message intermittently displayed in the status bar and appropriate intervals.
See images below:
1.jpg
2.jpg
3.jpg

The workbook and code posted is on an old template as I did not save the last changes I made to your current macro, so please just look out for the examples I place in the code that contain the statusbar messages. I tried to highlight this symbol: '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Attached is the old template where you can copy the statusbar lines into your current macro...
Hope this helps

hello my friend !!! could you help me again? i'm trying to figure out how to short my basic file by consignee.
this has to be done before we do the split into weight-based files...
the final result? if someone has more than 1 parcels,to gather them together,so it will be easier to manage.
is this too hard to be done?
thanks in advice...
by the way, i own you one,you saved my life !!!
have a very nice day!!

Hi,

I'm glad to have helped, but surely it was not as dramatic as you say it was LOL

Could you send me your latest file with a sample of data that illustrates your issue and that I can work with. I'll have to try figure out the best way to shorten the list. Pivot tables can do this quickly and accurately but depending on the data complexity, other techniques might be easier to code...

good afternoon...seems i finally managed to do the last step by myself....it was a really busy week!
untill next time,take care!

Excellent... It was quite a journey.
Stay well.

Goooooood morning!!! how r u my friend? hope everything's fine....
well,after so long,i'm back with some fresh ideas....could you assist?
just to remind,we got a macro that cleans up unneeded stuff,then sorts by name...
what i would like to do now...if possible,we could color the double (or triple or so..) consignee names,so it would be much easier to find and separate..
furthermore,it would be great if i could split these double records to a brand new excel file.....lets say "double shipments" or something....
could you give me some directions? i attach the final file that i use....