VLOOKUP over 2 criteria

[bond002]

Hi there,

I've got a massive data set to analyse and I need to make the set usable first. Unfortunately the records that refer to a unique identifier (MRP#) are split over several rows. Please see example.

What I need to do is to transpose some of the data over from rows to the columns.

Say the MRP# appears in 4 separate rows with a set operation (in this case CUT, WELD, FAB, PAIN). If i have a record with operation CUT, I want to find the corresponding MRP# for FAB and copy the data across. So I believe in this case the criteria will be both the MRP# and phrase "FAB STEEL".

I know that VLOOKUP won't work in this case. I've also tried INDEX and MATCH but it didn't work so I must be doing something wrong. Please help!

Thanks in advance!

K

[Czeslaw]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Put formula in cell M2 and pull it to the right and down.

[JohnTopley]

re Czeslaw's reply

Change ";" to ","

=IFERROR(INDEX(D$2:D$14,AGGREGATE(15,6,ROW($A$1:$A$30)/($C$2:$C$14=$L2),ROW(A1))),"")

[bond002]

Hey guys,

first of all thanks and dzięki for you help.

I plugged both of the formulas (sheet 1 and sheet 2) and it doesn't seem to be working in neither of the cases. Would it be easier to have an additional column that would determine which rows to copy / look at (i.e only perform formula when B="FAB STEEL"?

Best,
K

[FDibbins]

Why do you say that =IFERROR(INDEX(D$2:D$14,SMALL(INDEX(ROW(A$1:A$13)*($C$2:$C$14=$L2),0),COUNTIF($C$2:$C$14,"<>"&$L2)+ROW(A1))),"") is not working?
What did you expect and what did you get?

	L	M
1		FAB STEEL Estimate Hours
2	FAB STEEL	54.4
3	FAB STEEL	54.4
4	FAB STEEL	46.36
5	FAB STEEL	54.4
6	FAB STEEL

[bond002]

Hi Ford,

I got exactly what you showed above. Please see below for what I was after. It seems that the formula is not matching exactly to column A and it's ignoring row 4 and below...

L M
1 FAB STEEL Estimate Hours
2 FAB STEEL 54.40*
3 FAB STEEL 54.40*
4 FAB STEEL 54.40*
5 FAB STEEL 54.40*
6 FAB STEEL 54.40*
7 FAB STEEL 54.40*
8 FAB STEEL 46.36*
9 FAB STEEL 46.36*
10 FAB STEEL 46.36*
11 FAB STEEL 54.40*
12 FAB STEEL 54.40*
13 FAB STEEL 54.40*
14 FAB STEEL 54.40*

Best,
K

[FDibbins]

Not sure why you say that. You only 4 entries that are FAB STEEL, the others are CUT and WELD STEEL - or do you want them pulled as well?

Below I have changed the hours to letters so you can see what data is being pulled...

	C	D	E	F	G	H	I	J	K	L	M
1	Op Seq	Estimate Hours	Actual Hours	Issued to Production	Production Start Date	Production Start Time	Production End Date	Production End Time	Product Despatch Date		FAB STEEL Estimate Hours
2	CUT STEEL	10.58	13.90	16/10/2015	03/11/2015	00:00:00	24/11/2015	08:26:07		FAB STEEL	aaa
3	FAB STEEL	aaa	116.82	16/10/2015	03/11/2015	00:00:00	03/12/2015	09:20:39		FAB STEEL	bbb
4	WELD STEEL	12.00	22.17	16/10/2015	12/11/2015	00:00:00	28/11/2015	09:52:30		FAB STEEL	ccc
5	CUT STEEL	0.00	2.85	16/10/2015	13/11/2015	00:00:00	19/11/2015	00:00:00		FAB STEEL	ddd
6	FAB STEEL	bbb	95.24	16/10/2015	16/11/2015	00:00:00	26/11/2015	17:58:42		FAB STEEL
7	WELD STEEL	22.70	23.61	16/10/2015	19/11/2015	09:56:52	24/11/2015	00:00:00		FAB STEEL
8	CUT STEEL	9.13	11.35	16/10/2015	21/10/2015	16:57:57	04/11/2015	00:00:00		FAB STEEL
9	FAB STEEL	ccc	157.97	16/10/2015	21/10/2015	00:00:00	16/11/2015	00:00:00		FAB STEEL
10	WELD STEEL	2.00	2.57	16/10/2015	03/11/2015	17:00:08	16/11/2015	17:00:59		FAB STEEL
11	CUT STEEL	10.58	12.93	16/10/2015	27/10/2015	16:59:40	23/11/2015	17:03:11		FAB STEEL
12	FAB STEEL	ddd	68.26	16/10/2015	27/10/2015	18:04:42	06/11/2015	00:00:00		FAB STEEL
13	WELD STEEL	22.70	24.78	16/10/2015	04/11/2015	18:00:00	09/11/2015	00:00:00		FAB STEEL
14	PAINT STEEL	1.51	1.85	16/10/2015	12/11/2015	14:06:15	12/11/2015	14:06:15		FAB STEEL

[JohnTopley]

Are you wanting the SAME "FAB STEEL" data (say for MRP# V7737-23697) copied 3 times? Why???

Please post a file with manually entered expected output (as we appear to be uncertain by what you want).

[bond002]

Thanks guys for your help so far!

Sorry guys, it’s hard to explain the process when I only just start getting my head around it myself.

So a bit of a background…

For each of the Unique MRP# there are anything between 1-7 operations that should happen in sequence (CUT – FAB – WELD – PAINT – etc). It always start with CUT STEEL so CUT STEEL should be the first record in the row. Unfortunately the system that I use creates a new line item for every operation that happens to the Unique MRP#. So say for MRP# V7737-23697 I will have 3 rows of data for CUT, FAB and WELD. Currently my database has over 22000 rows to analyse and I need to analyse each of the Unique MRP# separately (or in batches based on further criteria).

In order to analyse my process (that btw is not working great ;-) ) I need to somehow copy across the data leaving the first part of the sheet unchanged as I will be adding data with time.

Please see attached an example of what I would ideally see as a result.

Best regards,
Krzys

[bond002]

Ok, i think I cracked it!

This is what I put in M3:

{=INDEX(E$3:E$15,MATCH(1,(B$3:B$15=B3)*(D$3:D$15=M3),0))}

seems to be working

[samba_ravi]

L2=

Please Login or Register  to view this content.

M2

Please Login or Register  to view this content.

COPY AND PASTE M2 towards right
and copy L2:R2 and paste towards down