Sort out lists with VLOOKUP

[gussen]

Hello everybody,

I would like to let excel sort out two lists for me with item ID's, revisions and last saved date. i think it's possible with the VLOOKUP function but i don't know for sure. Please look at the example below and let me know if it's possible to do this by excel formula's.

Startlist:[TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Filters[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1012[/TD]
[TD]AA[/TD]
[TD]2012-10-09[/TD]
[TD][/TD]
[TD]1011[/TD]
[TD]AA[/TD]
[TD]2012-10-09[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1013[/TD]
[TD]AA[/TD]
[TD]2012-10-10[/TD]
[TD][/TD]
[TD]1012[/TD]
[TD]AA[/TD]
[TD]2012-10-09[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1014[/TD]
[TD]AA[/TD]
[TD]2012-10-10[/TD]
[TD][/TD]
[TD]1013[/TD]
[TD]AA[/TD]
[TD]2012-10-10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1015[/TD]
[TD]AB[/TD]
[TD]2012-10-15[/TD]
[TD][/TD]
[TD]1015[/TD]
[TD]AA[/TD]
[TD]2012-10-10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1016[/TD]
[TD]AA[/TD]
[TD]2012-10-12[/TD]
[TD][/TD]
[TD]1016[/TD]
[TD]AB[/TD]
[TD]2012-10-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1017[/TD]
[TD]AA[/TD]
[TD]2012-10-10[/TD]
[TD][/TD]
[TD]1017[/TD]
[TD]AA[/TD]
[TD]2012-10-12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1018[/TD]
[TD]AA[/TD]
[TD]2012-10-11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Needed result:[TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Filters[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1011[/TD]
[TD]AA[/TD]
[TD]2012-10-09[/TD]
[TD][/TD]
[TD]1b[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1012[/TD]
[TD]AA[/TD]
[TD]2012-10-09[/TD]
[TD][/TD]
[TD]1012[/TD]
[TD]AA[/TD]
[TD]2012-10-09[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1013[/TD]
[TD]AA[/TD]
[TD]2012-10-10[/TD]
[TD][/TD]
[TD]1013[/TD]
[TD]AA[/TD]
[TD]2012-10-10[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1014[/TD]
[TD]AA[/TD]
[TD]2012-10-10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1a[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1015[/TD]
[TD]AB[/TD]
[TD]2012-10-15[/TD]
[TD][/TD]
[TD]1015[/TD]
[TD]AA[/TD]
[TD]2012-10-10[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1016[/TD]
[TD]AA[/TD]
[TD]2012-10-12[/TD]
[TD][/TD]
[TD]1016[/TD]
[TD]AB[/TD]
[TD]2012-10-15[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1017[/TD]
[TD]AA[/TD]
[TD]2012-10-10[/TD]
[TD][/TD]
[TD]1017[/TD]
[TD]AA[/TD]
[TD]2012-10-12[/TD]
[TD][/TD]
[TD]1a[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1018[/TD]
[TD]AA[/TD]
[TD]2012-10-11[/TD]
[TD][/TD]
[TD]1b[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Meaning is to sort the list by item nr. but keeping cels Ax-Cx and Ex-Gx together.
Column I: i want to use to see if the item is in list 1a/1b or both(2)
Column J: If listed on both compare Bx to Fx, same true, different false
Column K: If listed on both compare Cx to Gx, same true, different false

Above the lists should be filters to search.

my main concern is to get the list sorted like in cells A1-G8, rest i already know how to solve!

Anyone an idea to solve this?

 

[p45cal]

originals in columns A:G, new lists in I:N
Note that he formula in I2 is ARRAY-ENTERED (Ctrl+Shift+Enter, not just Enter)
Copy the formulae down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Filters									List 1			List 2
2	1012	AA	09/10/2012		1011	AA	09/10/2012		1011				AA	09/10/2012
3	1013	AA	10/10/2012		1012	AA	09/10/2012		1012	AA	09/10/2012		AA	09/10/2012
4	1014	AA	10/10/2012		1013	AA	10/10/2012		1013	AA	10/10/2012		AA	10/10/2012
5	1015	AB	15/10/2012		1015	AA	10/10/2012		1014	AA	10/10/2012
6	1016	AA	12/10/2012		1016	AB	15/10/2012		1015	AB	15/10/2012		AA	10/10/2012
7	1017	AA	10/10/2012		1017	AA	12/10/2012		1016	AA	12/10/2012		AB	15/10/2012
8					1018	AA	11/10/2012		1017	AA	10/10/2012		AA	12/10/2012
9									1018				AA	11/10/2012
Sheet

If you see 1E+100 in column I you've copied down too far.
It assumes there are no repeats within column A (same goes for column E).