organise multiple column entries to rows?

[dappy]

Hi folks, i'm trying to get to a stage where i can do a vlook up and the simplest way i can think of is to organise the column information from column to row, so i have only one entry for each person, although i need the row value also to be sorted lowest value on the left. is this possible?

[TABLE="width: 651"]
<tbody>[TR]
[TD]name[/TD]
[TD]value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name[/TD]
[TD]value[/TD]
[TD]value[/TD]
[TD]value[/TD]
[TD]value[/TD]
[/TR]
[TR]
[TD]person1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]person1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]peson2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="colspan: 2"]goes to ->[/TD]
[TD][/TD]
[TD]person2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]person1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]peson4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]person1[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]person2[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]peson4[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]peson4[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

thanks in advance!

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this helps.
Formula in D2 is copied down as far as you might need (after checking the rows - 100 in my formula - at least cover how big your data is)
Formula in E2 is copied across and down (again checking the rows referred to first)

Excel Workbook
	A	B	C	D	E	F	G	H
1	name	value		name
2	person1	2		person1	1	2	4
3	person2	3		person2	3	11
4	person1	1		peson4	4	5
5	person1	4
6	person2	11
7	peson4	4
8	peson4	5
9
Rearrange Data

 

[dappy]

Thanks Peter that is really fantastic, does the job perfectly!

I've been racking my brain for days over this

thanks again

Carl

 

[Peter_SSs]

Thanks Peter that is really fantastic, does the job perfectly!

I've been racking my brain for days over this

thanks again

Carl

No problem - glad to help.

 

[dappy]

Hi Peter,

Many thanks for your code, it works perfectly however I've issues :D

I've put it into a module and i'm getting a runtime error.

this is correct though right?

Range("D2").Formula = "=IFERROR(INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$100)+(A$2:A$100=""),0),0)),"")"

thanks again in advance

Carl

 

[Peter_SSs]

this is correct though right?

Range("D2").Formula = "=IFERROR(INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$100)+(A$2:A$100=""),0),0)),"")"

No, when inserting a formula like that with vba, you need to double-up the quote marks

Range("D2").Formula = "=IFERROR(INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$100)+(A$2:A$100=""""),0),0)),"""")"

 

[dappy]

No, when inserting a formula like that with vba, you need to double-up the quote marks

Range("D2").Formula = "=IFERROR(INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$100)+(A$2:A$100=""""),0),0)),"""")"

Thanks again Peter. This forum is so popular, so many questions and this one disappeared way down the list quite quickly so cheekily i asked in a different question and was answered by Rory.

thanks again chaps

Carl

 

[Peter_SSs]

Thanks again Peter. This forum is so popular, so many questions and this one disappeared way down the list quite quickly so cheekily i asked in a different question and was answered by Rory.

thanks again chaps

Carl

Glad you got your answer, but for the future, please be aware of #12 of the Forum Rules. Since I didn't know of the duplicate question, it seems my time answering here was really wasted.

 

[dappy]

Glad you got your answer, but for the future, please be aware of #12 of the Forum Rules. Since I didn't know of the duplicate question, it seems my time answering here was really wasted.

Hi Peter,

Understood and sincere apologies for wasting you time.

Carl