Distibuting an imported list to bands/brackets

colm

Board Regular
Joined
Jun 11, 2004
Messages
96
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

colm

Board Regular
Joined
Jun 11, 2004
Messages
96
I've tried searching but it's difficult to know what to search under.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,493
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJK
1NameVolumeDistanceBracketBracket CountBracketNameVolumeDistanceBracket
2Tom34,0001611-2011-20#10-10    
3Michael30,0001811-2011-20#211-20Tom340001611-20
4James29,0001811-2011-20#311-20Michael300001811-20
5Tim45,0002221-3021-30#111-20James290001811-20
6John15,0002421-3021-30#211-20    
7Joe25,0004241-5041-50#121-30Tim450002221-30
8Kevin16,0004841-5041-50#221-30John150002421-30
921-30    
1031-40    
1131-40    
1241-50Joe250004241-50
1341-50Kevin160004841-50
Distribute List
 

colm

Board Regular
Joined
Jun 11, 2004
Messages
96
Thank you very very much Peter,

I really thought no-one would reply.


I'll have a look at this now.


Colm
 

colm

Board Regular
Joined
Jun 11, 2004
Messages
96

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,493
Office Version
  1. 365
Platform
  1. Windows
colm said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,760
Messages
5,574,087
Members
412,566
Latest member
TexasTony
Top