How to take data from one sheet and rearrange it onto another sheet in specific layout.

[thatjasonr]

Hi All,

Basically I have an excel sheet of data, and I would like to represent this data in other layout (template) so that it is easier for a someone to read.

The basic two functions it must do:

a) able to only pull the necessary data
b) able to select a different column of data when referring to it.

I've attached a small example of what i'm trying to achieve more or less.

Any help will be greatly appreciated, ive tried Index, Match, V and Hlookup functions and just cant come right.

Regards,

Jason

Example.xlsx

[Ace_XL]

See attached. Formulas in yellow highlighted range

The formulas vary across the 3 sets due to differing ranges

[samba_ravi]

K11=IFERROR(INDEX($A:$A,SMALL(INDEX(((LEFT($A$4:$A$24,3)<>"set")+(INDEX($A$4:$H$24,,MATCH(M$10,$3:$3,0))="")=2)*10^10+ROW($C$4:$C$24),0),ROW($A1))),"")

L11=IFERROR(IF(VLOOKUP(K11,$A$4:$H$24,2,0)=0,"",VLOOKUP(K11,$A$4:$H$24,2,0)),"")

M11=IFERROR(IF(VLOOKUP(K11,$A$4:$H$24,MATCH(M$10,$3:$3,0),0)=0,"",VLOOKUP(K11,$A$4:$H$24,MATCH(M$10,$3:$3,0),0)),"")

TRY THE ABOVE AND COPY TOWARDS DOWN

[thatjasonr]

Hi Ace_XL

The example you sent looks great, thanks so much. Ill give it a bash with the real data and hopefully I come right. Ill Keep you updated!

I see you also used a combination of Small, Index, Match and Row functions. I was playing around with these and just got no where. Ill see if I can make sense of what you did