split column into multiple files into multiple columns based on another file

[Hasson]

hi
I would split column B into files PUR1,PUR2 into multiple columns into column B,C,D,E based on splited data file SEARCH into columns B,C,D . the result should be in first sheet into files PUR1,2 and should replace the data every time run the macro . I put the expected result in both the files in sheet result . I have about five files in the same folder whit the same structure as in PUR1,2

thanks in advance

[JohnTopley]

Is there any reason why you have separate workbooks rather than putting all the data in one workbook with 2 sheets per "PURx"??

See attached

Named range "Key" based on formula in H of "RP"

=D2& " " &E2& " " &F2

in c2 of "PUR1B"

=IFERROR(INDEX(RP!D$2:D$1000,MATCH(PUR1A!$B2,Key,0)),"")

copy across to E

in F2

=IFERROR(INDEX(PUR1A!E$2:E$1000,MATCH(PUR1A!$B2,PUR1A!$B$2:$B$1000,0)),"")

[JohnTopley]

VBA version

Please Login or Register  to view this content.

Assumes ALL files in the selected folder are to be processed.

Ensure TAB names are consistent (currently "Report" and "Result")

[Hasson]

Is there any reason why you have separate workbooks rather than putting all the data in one workbook with 2 sheets per "PURx"??

yes , theses files issued by another company and sent by email throught each month and differnt date .
the result are put into files just are simple to undersatnd what I want, but my real data for each file are about 500 rows and it will take more time copy & paste in one file . so that's why I look for macro achieve that.
as to post#3 I prefer VBA I will test it and inform you how works .

[Hasson]

thanks . I would as I siad in OP

the rsult should be in first sheet into files PUR1,2

the sheet RESULT just to see how could be . the result should just be in the first sheet for all files are existed in the folder.
last thing I don't want selected folder way by browser . I would do that automatically without select folder .

[Hasson]

another choice experts?

[Hasson]

cross posted https://www.mrexcel.com/board/thread...-file.1193454/
no answer so far and edited the file

[Hasson]

I would add note , if the items in column B into files PUR1,PUR2 are not matched with columns B,C,D, based on splited data file SEARCH then should not split and show message " there items are not matched you should correct them before split "