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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0
Thank you very very much Peter,

I really thought no-one would reply.


I'll have a look at this now.


Colm
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,134
Messages
6,106,141
Members
447,996
Latest member
ANDYADAM

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