Distibuting an imported list to bands/brackets

[colm]

Apologies if this looks messy.


I recieve a table in Excel from a coleague each day like this(minus the dots).


Name...........Volume.....Distance.....Bracket
Tom.............34,000.....16.............11-20
Michael.........30,000.....18.............11-20
James..........29,000......18............11-20
Tim..............45,000.....22.............21-30
John............15,000......24............21-30
Joe..............25,000......42............41-50
Kevin...........16,000......48............41-50

I need to distribute the rows to line up along a preset column of brackets that looks like this

0-10
0-10
0-10
0-10
0-10
0-10
0-10
0-10
0-10
0-10
0-10
0-10
0-10
0-10
0-10
11-20 Tom.............34,000.....16.............11-20
11-20 Michael.........30,000.....18.............11-20
11-20 James..........29,000......18............11-20
11-20
11-20
11-20
11-20
11-20
11-20
11-20
11-20
11-20
11-20
11-20
11-20
11-20
21-30 Tim..............45,000.....22.............21-30
21-30 John............15,000......24............21-30
21-30
21-30
21-30
21-30
21-30
21-30
21-30
21-30
21-30
21-30
21-30
21-30
21-30
31-40
31-40
31-40
31-40
31-40
31-40
31-40
31-40
31-40
31-40
31-40
31-40
31-40
31-40
31-40
41-50 Joe..............25,000......42............41-50
41-50 Kevin...........16,000......48............41-50
41-50
41-50
41-50
41-50
41-50
41-50
41-50
41-50
41-50
41-50
41-50
41-50
41-50

The column of brackets needs to stay like this as there are calculations that are run off these exact positions.

At the moment I am dragging and dropping the rows. I need a faster way to do this.

I've tried using a look-up table but it involves endless ammounts of IF statements. Maybe VB could help or another formula or nest of formulas.


Apologies if the answer is obvious.

Colm

 

[colm]

I've tried searching but it's difficult to know what to search under.

 

[Peter_SSs]

Colm

Here is one way. In the results section (columns G:K on my sheet) I have reduced the number of rows in each section to allow display on this board.

I have used a 'helper' column (column E) but this column could be hidden if desired.

The formula in E2 (copied down) is:
=D2&"#"&COUNTIF(D$1:D2,D2)

The formula in H2 (copied across and down) is:
=IF(ISNUMBER(MATCH($G2&"#"&COUNTIF($G$1:$G2,$G2),$E:$E,0)),INDEX($A:$D,MATCH($G2&"#"&COUNTIF($G$1:$G2,$G2),$E:$E,0),COLUMN()-COLUMN($G2)),"")

Mr Excel 05 11 21.xls
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Volume	Distance	Bracket	Bracket Count		Bracket	Name	Volume	Distance	Bracket
2	Tom	34,000	16	11-20	11-20#1		0-10
3	Michael	30,000	18	11-20	11-20#2		11-20	Tom	34000	16	11-20
4	James	29,000	18	11-20	11-20#3		11-20	Michael	30000	18	11-20
5	Tim	45,000	22	21-30	21-30#1		11-20	James	29000	18	11-20
6	John	15,000	24	21-30	21-30#2		11-20
7	Joe	25,000	42	41-50	41-50#1		21-30	Tim	45000	22	21-30
8	Kevin	16,000	48	41-50	41-50#2		21-30	John	15000	24	21-30
9							21-30
10							31-40
11							31-40
12							41-50	Joe	25000	42	41-50
13							41-50	Kevin	16000	48	41-50
Distribute List

 

[colm]

Thank you very very much Peter,

I really thought no-one would reply.


I'll have a look at this now.


Colm

 

[colm]

Well that seems to work very well. I have some changes to make but it works fine. Thanks again Peter,

Have you come across this problem before or did you have to work through it all for the first time?


Colm

 

[Peter_SSs]

Well that seems to work very well. I have some changes to make but it works fine. Thanks again Peter,

Have you come across this problem before or did you have to work through it all for the first time?


Colm

No, I haven't come across this particular problem before. Just putting knowledge gained on this board and elsewhere to use, along with some experimentation to see what works.

 

[colm]

Well done, thanks again.

COlm