Excel 2003 sorting lot of data, multiply sheets by date

I have to sort lot of expiry dates , by the oldest one form 6 workbooks.
Problem is on the same code in one or more sheets i have multiple values, so i cant use value lookup.
At the moment i link directly every cell, but if someone adds rows everything will go wrong.

Example

Workbook 1

Code . amount . expiry date . days to expire . amout . expiry date . days to expire . amout . expiry date . days to expire
391 . 10 . 25-05-14 . 31 . 50 . 26-05-14 . 32 . 100 . 28-05-14 . 34
391 . 15 . 25-09-14 . 61 . 60 . 29-05-14 . 39 . 600 . 01-06-14 . . 72
391 . 25 . 15-09-14 . 61 . 55 . 19-05-14 . 39 . 622 . 01-09-14 . 72
392 . 20 . 01-08-14 . 31 . 70 . 30-05-14 . 38 . 650 . 02-06-14 . 55
393 . 20 . 01-08-14 . 31 . 70 . 30-05-14 . 38 . 650 . 02-06-14 . 55

Problem is in this example code 391, sometimes i have 7-8 rows of same code, and valuelookup cant help, also i need to match code
if someone inserts rows i dont do it all again.
Where I hawe one row, i can do value lookup to main workbook to sort all data for one code to colms like

code amout exp days
amout exp days
amout exp days

and then with pivot sort them by the worksheet and oldest date like :
code
workbook1
amout exp days (oldest date from workbook 1 for that code)
amout exp days (next oldest date)
amout exp days
amout exp days
workbook2
amout exp days(oldest date from workbook 2 for that code)
amout exp days
amout exp days

I just entered few rows, but i have about 10-15 codes that i need to sort
I can attach some tables if needed if you cant understand my problem.
Maybe I should use some macros or something ??

Ok, so this looks to complicated or not understanding so i put one example workbook to make it easier to understand.
I made to do index and match, but i still got value just for first row like in vlookup !!

Thanks for quick reply !!

I need to sort it horizontally like :
TP količina Datum isteka proizvoda Preostalo dana trajanja
TP količina Datum isteka proizvoda Preostalo dana trajanja
TP količina Datum isteka proizvoda Preostalo dana trajanja
TP količina Datum isteka proizvoda Preostalo dana trajanja

So i can merge all with pivot table.
You can see it in my example sheet 2 (i need to sort like this)

When I use this code on original workbook I just get some small mount of data sorted (only 11 rows of data)
How to spred this code on entire workbook ?

Originally Posted by oeldere

Try this code in a dummy of your sheet.

Please Login or Register  to view this content.

Here in your example in sheet1 you have 37 diferent rows of data, and output shows only 24, so something is missing.

Maybe thats why on original workbook i dont get all data too.The biggest workbook has 210 rows.

THANKS AGAIN !!!!

Originally Posted by oeldere

See the attached file.

Each row has five colms to be sorted, so in output sheet we need to have 75 rows and we have only 61.
With data (non blanks) in output folder we have 24 rows and in sheet1 we have 37 fields of data .


Thanks alot for help !!!

You attached file in post 7 as you said.In that file we have "sheet1"" from which we sort data and there we have 15 rows.Each row has 15 colms, and we need to sort them 3 by 3.
Colm D TP kolicina
Colm E Datum isteka proizvoda
Colm F preostalo dana trajanja

And it has to look like this

TP količina Datum isteka proizvoda Preostalo dana trajanja
TP količina Datum isteka proizvoda Preostalo dana trajanja
TP količina Datum isteka proizvoda Preostalo dana trajanja
TP količina Datum isteka proizvoda Preostalo dana trajanja
TP količina Datum isteka proizvoda Preostalo dana trajanja
TP količina Datum isteka proizvoda Preostalo dana trajanja
TP količina Datum isteka proizvoda Preostalo dana trajanja
TP količina Datum isteka proizvoda Preostalo dana trajanja

Entire workbook not just part of it !!!!!

Check the sheet1 (field D86 709, field E86 28-Oct-14, field f86 175 ) all this doesnt exist in output folder, that means its not all sorted.

Now it works great !
THANK YOU!!!

Just I must change i=6 and all sorted perfect.
I have 2 more questions.
Do I have to rename every sheet to sheet1, or I can change sheet1 to curent sheet, so the sheet I am in runs the macro.
How to insert modules in some my workbook ? When I insert code in my workbok, I dont have option to run macro (so i always run it from your sample).And how to rename module, it always has full name jevtabg(oeldere5)....

Sent from Huawei P6

Clear and well !!!

You are the man !!!

Thanks again !!!



Sent from Huawei P6