Conditional Vlook or use of Match/Index

R

ravikumars

New Member
Joined
Mar 5, 2015
Messages
4
I want to convert an input to desired output. Kindly help. In the output - the columns value should start from most recent (year)
Input -
https://docs.google.com/spreadsheets/d/1m6t5UczZHK1lWAVuIVY2Amu8H8zQK6G8pJrsO-8HSPc/edit#gid=0



INPUT
EmpNo
Others - Rajasthan University, 2010
Diploma- ICG college Jaipur, 2008
12th - St.Anselam's, 2004
MCA - IGNOU, 2004
Diploma - IGNOU, 2003
Diploma - NIIT, 1999
B.Com - Delhi University, 1999

<tbody>
[TD="align: center"]Year[/TD]
[TD="align: center"]Qualification[/TD]

[TD="align: right"]125[/TD]
[TD="align: right"]2010[/TD]

[TD="align: right"]125[/TD]
[TD="align: right"]2008[/TD]

[TD="align: right"]125[/TD]
[TD="align: right"]2004[/TD]

[TD="align: right"]501[/TD]
[TD="align: right"]2004[/TD]

[TD="align: right"]501[/TD]
[TD="align: right"]2003[/TD]

[TD="align: right"]501[/TD]
[TD="align: right"]2001[/TD]

[TD="align: right"]501[/TD]
[TD="align: right"]1999[/TD]

</tbody>


OUTPUT
EmpNoQualification4
Others - Rajasthan University, 2010Diploma- ICG college Jaipur, 200812th - St.Anselam's, 2004
MCA - IGNOU, 2004Diploma - IGNOU, 2003Diploma - NIIT, 1999B.Com - Delhi University, 1999

<tbody>
[TD="align: center"]Qualification1[/TD]
[TD="align: center"]Qualification2[/TD]
[TD="align: center"]Qualification3[/TD]

[TD="align: right"]125[/TD]

[TD="align: right"]501[/TD]

</tbody>
 
Last edited:
Sort by date Sort by votes
[TABLE="width: 752"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]EmpNo[/TD]
[TD]Year[/TD]
[TD]Qualification[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD="align: right"]2010[/TD]
[TD]Others - Rajasthan University, 2010[/TD]
[TD]Diploma- ICG college Jaipur, 2008[/TD]
[TD]12th - St.Anselam's, 2004[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD="align: right"]2008[/TD]
[TD]Diploma- ICG college Jaipur, 2008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD="align: right"]2004[/TD]
[TD]12th - St.Anselam's, 2004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]501[/TD]
[TD="align: right"]2004[/TD]
[TD]MCA - IGNOU, 2004[/TD]
[TD]Diploma - IGNOU, 2003[/TD]
[TD]Diploma - NIIT, 1999[/TD]
[TD]B.Com - Delhi University, 1999[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]501[/TD]
[TD="align: right"]2003[/TD]
[TD]Diploma - IGNOU, 2003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]501[/TD]
[TD="align: right"]2001[/TD]
[TD]Diploma - NIIT, 1999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]501[/TD]
[TD="align: right"]1999[/TD]
[TD]B.Com - Delhi University, 1999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]just using formulas I can get to here[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
oldbrewer said:
[TABLE="width: 752"]
<tbody>[TR]
[TD]EmpNo[/TD]
[TD]Year[/TD]
[TD]Qualification[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD="align: right"]2010[/TD]
[TD]Others - Rajasthan University, 2010[/TD]
[TD]Diploma- ICG college Jaipur, 2008[/TD]
[TD]12th - St.Anselam's, 2004[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD="align: right"]2008[/TD]
[TD]Diploma- ICG college Jaipur, 2008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD="align: right"]2004[/TD]
[TD]12th - St.Anselam's, 2004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]501[/TD]
[TD="align: right"]2004[/TD]
[TD]MCA - IGNOU, 2004[/TD]
[TD]Diploma - IGNOU, 2003[/TD]
[TD]Diploma - NIIT, 1999[/TD]
[TD]B.Com - Delhi University, 1999[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]501[/TD]
[TD="align: right"]2003[/TD]
[TD]Diploma - IGNOU, 2003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]501[/TD]
[TD="align: right"]2001[/TD]
[TD]Diploma - NIIT, 1999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]501[/TD]
[TD="align: right"]1999[/TD]
[TD]B.Com - Delhi University, 1999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]just using formulas I can get to here[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Could you please explain this sir?
 
Upvote 0
Just using formulas I can get the result as shown but clearly there are now blank rows against the second and third 125 rows (by using edit, copy, edit, paste, special, values we could lock the results and delete unwanted rows or maybe sort on 4th column pushing blanks to bottom
 
Upvote 0
[TABLE="width: 971"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]EmpNo[/TD]
[TD]Year[/TD]
[TD]Qualification[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD="align: right"]2010[/TD]
[TD]Others - Rajasthan University, 2010[/TD]
[TD]Diploma- ICG college Jaipur, 2008[/TD]
[TD]12th - St.Anselam's, 2004[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD="align: right"]2008[/TD]
[TD]Diploma- ICG college Jaipur, 2008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD="align: right"]2004[/TD]
[TD]12th - St.Anselam's, 2004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]501[/TD]
[TD="align: right"]2004[/TD]
[TD]MCA - IGNOU, 2004[/TD]
[TD]Diploma - IGNOU, 2003[/TD]
[TD]Diploma - NIIT, 1999[/TD]
[TD]B.Com - Delhi University, 1999[/TD]
[/TR]
[TR]
[TD="align: right"]501[/TD]
[TD="align: right"]2003[/TD]
[TD]Diploma - IGNOU, 2003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]501[/TD]
[TD="align: right"]2001[/TD]
[TD]Diploma - NIIT, 1999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]501[/TD]
[TD="align: right"]1999[/TD]
[TD]B.Com - Delhi University, 1999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]formula in D2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]=IF(A2<>A1,IF(A3=A2,C3,""),"")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]formula in E2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]=IF(A2<>A1,IF(A4=A2,C4,""),"")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]similar formulas in F2 and G2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]these were dragged down as far as row 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]so in D3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]=IF(A3<>A2,IF(A4=A3,C4,""),"")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]this detects that A3=A2 and so returns a blank[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
not sure what you mean - the formulas drag down 200,000 rows if necessary and if some people could have more than 4 qualifications simply put in a few more formulae in columns H,I,J,K

I would then copy paste special values and sort to move all blanks to the bottom and delete them
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Using VBA Replace Function to Edit Formulas
Replies
1
Views
1K
Fluff
Fluff
R
  • Question Question
Help!
Replies
3
Views
2K
63falcondude
63falcondude

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top