Vlookup and transpose the attributes as headers

[Parvathy]

Hi All ,

I am facing an issue with an excel automation .

I have the following excel Sheet …

idx	Group	Family	Quantity	Audit comments y/n
1​	Apple	Red	100​
2​	Apple	Green	20​
3​	Orange	Mandarin	100​
4​	Orange	Tangerine	88​
5​	Pineapple	Brecheche	60​
6​	Pineapple	Queen	70​
7​	Apple	Gala	44​

and another work book which consists of auditing attributes for each fruits category

Group audit	Audit variations
Orange	Size
Orange	Color
Orange	Number_Of_Items
Apple	Quantity
Apple	variations
Apple	Family
Apple	Defect
Pineapple	Size
Pineapple	Firmness
Pineapple	Special_Feature
Pineapple	Family
Pineapple	color
Watermelon	ripeness
Watermelon	Heaviness
Watermelon	Family
Watermelon	weight

And I want to the following result into another excel sheet

idx	Group	Family	Quantity	Audit comments y/n	Quantity	variations	Family	Defect
1​	Apple	Red	100​
2​	Apple	Green	20​
3​	Apple	Mandarin	100​
4​	Apple	Tangerine	88​
5​	Apple	Brecheche	60​
6​	Apple	Queen	70​
7​	Apple	Gala	44​

Right now, I am manually doing a lookup on the group category of the first data frame with the "group Audit " column on the second. Afterward doing a transpose on the audit variation based on the Group Type.Is there any way to automate this process?

This would save a lot of time. Suggestions are welcomed.

 

[ravi2628]

Hi Parvathy,

is this the logic you are looking at.

Vlookup to Map.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	idx	Group	Family	Quantity	Audit comments y/n	Color	Defect	Family	Firmness	Heaviness	Number_Of_Items	Quantity	ripeness	Size	Special_Feature	variations	weight
2	1	Apple	Red	100	Y	0	1	1	0	0	0	1	0	0	0	1	0
3	2	Apple	Green	20
4	3	Orange	Mandarin	100	Y	1	0	0	0	0	1	0	0	1	0	0	0
5	4	Orange	Tangerine	88
6	5	Pineapple	Brecheche	60
7	6	Pineapple	Queen	70	Y	1	0	1	1	0	0	0	0	1	1	0	0
8	7	Apple	Gala	44
Sheet1

Cell Formulas
Range	Formula
F1:U1	F1	=IFERROR(TRANSPOSE(INDEX(Sheet2!$B$2:$B$17, MATCH(SMALL(IF(COUNTIF($E$1:E$1, Sheet2!$B$2:$B$17)=0, COUNTIF(Sheet2!$B$2:$B$17, "<"&Sheet2!$B$2:$B$17), ""), 1), COUNTIF(Sheet2!$B$2:$B$17, "<"&Sheet2!$B$2:$B$17), 0))),"")
F2:U8	F2	=IF(AND($E2="Y",F$1<>""),COUNTIFS(Sheet2!$A:$A,$B2,Sheet2!$B:$B,F$1),"")

 

[Parvathy]

Hi Parvathy,

is this the logic you are looking at.

Vlookup to Map.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	idx	Group	Family	Quantity	Audit comments y/n	Color	Defect	Family	Firmness	Heaviness	Number_Of_Items	Quantity	ripeness	Size	Special_Feature	variations	weight
2	1	Apple	Red	100	Y	0	1	1	0	0	0	1	0	0	0	1	0
3	2	Apple	Green	20
4	3	Orange	Mandarin	100	Y	1	0	0	0	0	1	0	0	1	0	0	0
5	4	Orange	Tangerine	88
6	5	Pineapple	Brecheche	60
7	6	Pineapple	Queen	70	Y	1	0	1	1	0	0	0	0	1	1	0	0
8	7	Apple	Gala	44
Sheet1

Cell Formulas
Range	Formula
F1:U1	F1	=IFERROR(TRANSPOSE(INDEX(Sheet2!$B$2:$B$17, MATCH(SMALL(IF(COUNTIF($E$1:E$1, Sheet2!$B$2:$B$17)=0, COUNTIF(Sheet2!$B$2:$B$17, "<"&Sheet2!$B$2:$B$17), ""), 1), COUNTIF(Sheet2!$B$2:$B$17, "<"&Sheet2!$B$2:$B$17), 0))),"")
F2:U8	F2	=IF(AND($E2="Y",F$1<>""),COUNTIFS(Sheet2!$A:$A,$B2,Sheet2!$B:$B,F$1),"")

Hi Ravi
Than you for responding.

However i only need the attributes as headers without any values. Sorry, if i didn't made it clear in the previous post.The values will be entered by auditors.


Input File

idx	Group	Family	Quantity	Audit comments y/n
1​	Apple	Fuji	100​
2​	Apple	Gala	20​
3​	Apple	Honeycrisp	100​
4​	Apple	Brecheche	88​
5​	Apple	McIntosh	60​
6​	Apple	Gala	70​
7​	Apple	Gala	44​

Audit File :

Group audit	Audit variations
Orange	Size
Orange	Color
Orange	Number_Of_Items
Apple	Quantity
Apple	variations
Apple	Family
Apple	Defect
Pineapple	Size
Pineapple	Firmness
Pineapple	Special_Feature
Pineapple	Family
Pineapple	color
Watermelon	ripeness
Watermelon	Heaviness
Watermelon	Family
Watermelon	weight

Out file should look like:

idx	Group	Family	Quantity	Audit comments y/n	Quantity	variations	Family	Defect
1​	Apple	Fuji	100​
2​	Apple	Gala	20​
3​	Apple	Honeycrisp	100​
4​	Apple	Brecheche	88​
5​	Apple	McIntosh	60​
6​	Apple	Gala	70​
7​	Apple	Gala	44​

For example:

If the sheet contains only apple Group then ,audit attributes corresponding to Apple should be coming up in output data.

Once again thank you for your promt response.

 

[ravi2628]

Hi Ravi
Than you for responding.

However i only need the attributes as headers without any values. Sorry, if i didn't made it clear in the previous post.The values will be entered by auditors.


Input File

idx	Group	Family	Quantity	Audit comments y/n
1​	Apple	Fuji	100​
2​	Apple	Gala	20​
3​	Apple	Honeycrisp	100​
4​	Apple	Brecheche	88​
5​	Apple	McIntosh	60​
6​	Apple	Gala	70​
7​	Apple	Gala	44​

Audit File :

Group audit	Audit variations
Orange	Size
Orange	Color
Orange	Number_Of_Items
Apple	Quantity
Apple	variations
Apple	Family
Apple	Defect
Pineapple	Size
Pineapple	Firmness
Pineapple	Special_Feature
Pineapple	Family
Pineapple	color
Watermelon	ripeness
Watermelon	Heaviness
Watermelon	Family
Watermelon	weight

Out file should look like:

idx	Group	Family	Quantity	Audit comments y/n	Quantity	variations	Family	Defect
1​	Apple	Fuji	100​
2​	Apple	Gala	20​
3​	Apple	Honeycrisp	100​
4​	Apple	Brecheche	88​
5​	Apple	McIntosh	60​
6​	Apple	Gala	70​
7​	Apple	Gala	44​

For example:

If the sheet contains only apple Group then ,audit attributes corresponding to Apple should be coming up in output data.

Once again thank you for your promt response.

I think then you can use only the first formula to get the data

=IFERROR(TRANSPOSE(INDEX(Sheet2!$B$2:$B$17, MATCH(SMALL(IF(COUNTIF($E$1:E$1, Sheet2!$B$2:$B$17)=0, COUNTIF(Sheet2!$B$2:$B$17, "<"&Sheet2!$B$2:$B$17), ""), 1), COUNTIF(Sheet2!$B$2:$B$17, "<"&Sheet2!$B$2:$B$17), 0))),"")

 

[Parvathy]

Hi Ravi,

I am getting blank ouput when trying to enter the formula. I am not sure what mistake i made in this .

 

[ravi2628]

Hi Ravi,

I am getting blank ouput when trying to enter the formula. I am not sure what mistake i made in this .

View attachment 49234

Hi Parvathy,

Please share the SS of Sheet2 once because I have entered the data in following cells.

 

[Parvathy]

Hi Parvathy,

Please share the SS of Sheet2 once because I have entered the data in following cells.

View attachment 49238

Hi Ravi , PFA the SS of the sheet2.

Thank you so much for the reply.

 

[ravi2628]

View attachment 49242



Hi Ravi , PFA the SS of the sheet2.

Thank you so much for the reply.

In your Sheet Name, it is written as Sheet 2 and in my Sheet, it is written as Sheet2 there is a space between the words in your sheet


You can Check the both SS

 

[ravi2628]

In your Sheet Name, it is written as Sheet 2 and in my Sheet, it is written as Sheet2 there is a space between the words in your sheet


You can Check the both SS

does the solution work for you