Any alternative to INDEX and MATCH formula?

[cc9083]

Hi there.
I have the followings in my spreadsheet.
a) Column A: 900 serial nos of Work Orders. Each is unique.
b) Column B: Parties responsible for each Work Order no. Certain parties will have more than 1 serial no in Column A.
Note: Column A & B are the main reference.
c) Column C, D & E: List of parties from three different locations performing works base on serial nos in Column A.
I would like a list of serial nos performed by parties in Column C, D & E base on Column A. I can't use the autofilter or sort function on each individual parties as this is very time consuming and moreover the info keeps changing ever 2-3 days. What I require is a simple summary list where I just need to paste the info from Column C, D & E into a special table and it would list down the serial nos from Column A. I had tried the INDEX & MATCH functions but it won't work as I have some parties performing more than 1 serial number. Would it be possible to create a table to perform the said task. These data entry and retrieval has taken minimum 4 hours of my time everyday and it takes longer when certain lists are revised. Thanks in advance.

 

[Aladin Akyurek]

Hi there.
I have the followings in my spreadsheet.
a) Column A: 900 serial nos of Work Orders. Each is unique.
b) Column B: Parties responsible for each Work Order no. Certain parties will have more than 1 serial no in Column A.
Note: Column A & B are the main reference.
c) Column C, D & E: List of parties from three different locations performing works base on serial nos in Column A.
I would like a list of serial nos performed by parties in Column C, D & E base on Column A. I can't use the autofilter or sort function on each individual parties as this is very time consuming and moreover the info keeps changing ever 2-3 days. What I require is a simple summary list where I just need to paste the info from Column C, D & E into a special table and it would list down the serial nos from Column A. I had tried the INDEX & MATCH functions but it won't work as I have some parties performing more than 1 serial number. Would it be possible to create a table to perform the said task. These data entry and retrieval has taken minimum 4 hours of my time everyday and it takes longer when certain lists are revised. Thanks in advance.

Would you post a scaled down sample along wit the expected results?

 

[shemayisroel]

Hi & Welcome,

Seems doable, could you please post some sample data in order for the solution to be tailored to your data.

By the sounds of it you need some sort of lookup function that picks up all the returning results not just the 1st instance.

 

[shemayisroel]

I'm assuming the solution your referring to would be something on the lines of the below?

http://www.mrexcel.com/forum/showthread.php?p=1864715#post1864715

 

[shemayisroel]

duplicate

 

[Stormseed]

duplicate

I did not quite understand about what you are referring to as "duplicate" ? Whats up with that ?

 

[tusharm]

If I understand you correctly, you will get a solution through a PivotTable, though some may see it as a somewhat unusual use of a PT. Create one PT for each of the lists from cols. C, D, and E. In the first put col C and the serial# as the row fields. In the 2nd PT, put col D and the serial# as the row fields In the 3rd...you get the idea. {grin}

That's it. You are done -- other than whatever aesthetic cleanup you want. Whenever you want an updated list, just refresh the 3 PTs.

Hi there.
I have the followings in my spreadsheet.
a) Column A: 900 serial nos of Work Orders. Each is unique.
b) Column B: Parties responsible for each Work Order no. Certain parties will have more than 1 serial no in Column A.
Note: Column A & B are the main reference.
c) Column C, D & E: List of parties from three different locations performing works base on serial nos in Column A.
I would like a list of serial nos performed by parties in Column C, D & E base on Column A. I can't use the autofilter or sort function on each individual parties as this is very time consuming and moreover the info keeps changing ever 2-3 days. What I require is a simple summary list where I just need to paste the info from Column C, D & E into a special table and it would list down the serial nos from Column A. I had tried the INDEX & MATCH functions but it won't work as I have some parties performing more than 1 serial number. Would it be possible to create a table to perform the said task. These data entry and retrieval has taken minimum 4 hours of my time everyday and it takes longer when certain lists are revised. Thanks in advance.

 

[cc9083]

Hi there.
Thank you so much for the sharings. I'm particularly interested in shemayisroel post which links to another 2 posts.

http://www.mrexcel.com/forum/showthr...15#post1864715
http://www.mrexcel.com/forum/showthread.php?t=231882

However, I'm looking for columns rather than rows to present my findings. The last person in the last post had worked on the column presentation but it was rather incomplete. I don't understand what he's trying to say. Maybe someone here is able to help me on this. Regards.

 

[Aladin Akyurek]

Hi there.
Thank you so much for the sharings. I'm particularly interested in shemayisroel post which links to another 2 posts.

http://www.mrexcel.com/forum/showthr...15#post1864715
http://www.mrexcel.com/forum/showthread.php?t=231882

However, I'm looking for columns rather than rows to present my findings. The last person in the last post had worked on the column presentation but it was rather incomplete. I don't understand what he's trying to say. Maybe someone here is able to help me on this. Regards.

Try to post a tiny sample along with the expected results?

 

[cc9083]

Work Order / User Code /
0001 / A235
0002 / C212
0003 / A235
0004 / K902
0005 / C212

Expected Results

User Code 1 / Work Order
A235 / 2 (qty of Work Order) / 0001 / 0003 /
C212 / 2 (qty of Work Order) / 0002 / 0005 /
K902 / 1 (qty of Work Order) / 0004