copy & merge columns based on column across workbooks in master workbook

Hi
I search for macro to copy & merge columns (SALES & RET) across files . so should search for each column separately and merge duplicates items based on column B .
notice: sometimes there are items in file for column RET , but it doesn't existed in another file for column SALES so will show empty cells for column SALES as in MASTER file and the same thing there are items in file in column sales ,but it doesn't existed in column RET then will show empty cells in column RET and will have about 10 workbooks in the same folder with same structure as in attaching for three workbooks and every time run macro should create new sheets and increment based on first sheet like REP_2 , REP_3 ..... and so on
the result in MASTER file as I did it .
thanks

I'm still waiting for anybody has idea to I look for it .

You must first well elaborate your need in order there is nothing to guess …

so what's not clear for you ?
did you see the files inside ?
are the details are poor ?
what I look for the macro will search for columns (SALES & RET) across files and combine data from all of files and merge duplicates items in MASTER file
as I said the all of files are existed in the same folder "C:\Users\MY-CAM\Desktop\kalil"
and when run the macro should create new sheet by increment based on previous sheet . also should deal with blank cells if they are in columns (SALES & RET) across files .

Obviously missing in your initial post ‼

I thought the Excel could deal with files in the same folder without indicate to the driver

Where is saved MASTER workbook ?

in the same directory with rest of others files .

Why RTR workbook is not a .xlsx file like the two other source workbooks ?!

this is the only file xlsm extension I received by email , if it's problem I will convert to xlsx .

Originally Posted by KalilMe

I thought the Excel could deal with files in the same folder without indicate to the driver
in the same directory with rest of others files

Your bad as you forgot to clearly mention it ! As guessing can't be coding …

Originally Posted by KalilMe

should deal with blank cells if they are in columns (SALES & RET) across files .

What could that mean ? As a blank cell is equal to zero, what could be the concern ?

What could that mean ? As a blank cell is equal to zero, what could be the concern ?

I mean any item is existed in column RET is not necessary could be in column SALES . so when merge item for column RET will be empty cell in column SALES for the same item and the same thing when merge item for column SALES will be empty cell in column RET for the same item .

Thanks Marc L !
question : why doesn't sort as I put it COR-FF1,COR-FF2,COR-FF3....?

Thanks for the rep !
No sort first 'cause you did not mention it in your initial post …
And according of the bad design of this column my Excel versions can only apply a text sort
so for example COR-FF2 should be the item #227 just after COR-FF199 and just before COR-FF20 :

SortAlpha.jpg

But maybe your recent Excel version can do what you expect so try manually to sort the result
then if you succeed just activate the Macro Recorder and redo the same operations
in order you get your own additional code …

Another way : does COR-FF# is the real prefix, always the same or always same data structure like ??????# (6 characters before the number) ?
In this case you can use a helper column - or the useless ITEM column ! - to extract the number then sort on this column.

Or just removing 'COR-FF' (if unique prefix) from column CO-IT in order to apply the sort on this column …

does it mean it's not possible sort directly for this prefix structure like this ?

Please Login or Register  to view this content.

I did with this COR-FF002 and your code sorted as I wanted .
but why ?! what's relating zero to succeed sorting ?

to your bad designed column CO-IT but as it depends on what you did not explain !

Why yo consider bad designed column ? !
I gave you the final result in MASTER workbook , isn't enough ?!

Or if the name can change is there always a single worksheet within the source workbook ?

yes could be changed and a single worksheet within the source workbook ,but based on your code indicates first sheet , so I don't think need to know if the sheet name will change within source workbook(this point I forgot posted . in reality I wanted posting it , but I forgot mentioning).
BTW: if you see the result in MASER file and details for OP your code ignores shows result in first sheet as I did it(when clear data for the first sheet in MASTER file it doesn't show result just add new sheet and show result ignoring first sheet when it's empty except headers.)

see the file. it supposes importing data into first sheet "REP_1" from the first time run the macro and when run again after fill first sheet , then will add new sheet with increment number for next sheet , but the code will ignore first sheet and move to next sheet when run from first time .

Ok : your bad ! 'Cause you did not explain that in your initial post as you asked for « every time run macro should create new sheet

which bad ?!!!!!
how could increment sheets every time when run the macro without start from the first sheet ?
I don't make first sheet empty . I put the result in first sheet , then automatically increment based on first sheet .

and according to your initial post attachment which was not empty like your last attachment

that's clarifying what your code does it. I gave you result directly in initial post .

According to your data the obvious correct reference should be COR-FF0002 rather than COR-FF2 in order to be directly well sorted !

I will follow this way .
I don't have any idea about this sorting way . so this is not my bad because I don't know it .

COR-FF0002 was the explanation of the seven years old kid level text sort but
no need to change the column design if you just well answer to my questions / hints since post #11 !
If you do not understand anything, quote it and ask …

COR-FF0002 was the explanation of the seven years old kid level text sort

I don't know that .

but
no need to change the column design if you just well answer to my questions

Another way : does COR-FF# is the real prefix, always the same or always same data structure like ??????# (6 characters before the number) ?

absolutely not

So in your future threads with a better explanation do not forget to attach at least a well representative workbook !
That will help to get a quicker more targeted expected solution …

According to your last answer post #9 is now updated under these rules :

• Under Windows only.
  
  
• Source workbooks must be closed and located in same folder than MASTER workbook.
  
  
• Only MASTER workbook opened under Excel.
  
  
• You may get wrong results if a source workbook is opened in another Excel instance.
  
  
• Inconsistant data prefix before the number in column B.
  
  
• With an inconsistant source workbook worksheet name as the new way can not know what is the first worksheet
  so it scans all the already sorted workbook Names collection for its relative worksheets
  then consolidate data for any worksheet with matching headers …
  
  
• If MASTER workbook last worksheet A2:D2 is empty so this worksheet is reused to load the results rather than creating a new one.

thank for a new version , but unfortunately shows error invalid procedure call or argument in this line

Please Login or Register  to view this content.

my bad , sorry !
I put in standard module not in workbook module .
ok it works perfectly .
just question about for NumberFormat in this line

Please Login or Register  to view this content.

after run the macro some numbers don't show number format but when I change to this

Please Login or Register  to view this content.

will fix this problem. so why doesn't show for all numbers?

As I mix a positive number format and a text format so obviously when a number is not shown
so the obvious reason is it's not a positive number but negative and as there is no negative in your attachment !

I don't understand !
do you mean what shows in cells are not real numbers ?

As the cell display depends on to the cell formatting …
But you did not again confirm : the number is negative ?

There is no negative number.

As no issue on my side so did you try at least with exactly the same initial post attachment ?

as far as I know implementing in the same initial post attachment , but I will try again .

As it well works again on my side with your attachment …

this is really strange !!
what I see the problem just for CVF file , otherwise the others file are ok !

In the result worksheet, in which cells addresses the number is not displayed but can be seen via the formula bar ?

I highlighted cells don't show number format.

but can be seen via the formula bar ?

it doesn't also show the formula bar.

So with an empty cell your post #27 has no sense ‼ As the number format just displays the cell content …

if you suspect what I said in post#27 you can test it .

According to your last attachment except you have modified the number format the result is exactly the same than my post #9 demonstration …
So I have no clue since your post #27 of what you are talking about …
If my result is wrong then you should better elaborate technically why with details.

According to your last attachment except you have modified the number format the result is exactly the same than my post #9 demonstration …

yes

So I have no clue since your post #27 of what you are talking about …

what I'm talking about when modify the number format the result is exactly the same than my post #9 demonstration then will work why?!
I thought the problem could be from the cells don't contain numeric values , but seem the cells contain numeric values

If my result is wrong then you should better elaborate technically why with details.

your result is correct . the question is just number format.
I'm surprised to work for you with the the same initial post attachment based on post#9 !

Originally Posted by KalilMe

the result is exactly the same than my post #9 demonstration then will work why?!
I thought the problem

Your post #9 demonstration ?‼‼
So as yours you should know how to fix it !

As I can't find / see any problem …

Your post #9 demonstration ?‼‼

my apologies about this mistake !

So as yours you should know how to fix it !

I did it based on post#27

As I can't find / see any problem …

that's on your side , but for me makes me so confused . same files works for you but not work for me about number format .
for me I see this is mystery.