# Distibuting an imported list to bands/brackets

#### colm

##### Board Regular
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).
I've tried searching but it's difficult to know what to search under.

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

Thank you very very much Peter,

I really thought no-one would reply.

I'll have a look at this now.

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

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.

Well done, thanks again.

COlm

Replies
10
Views
199
Replies
1
Views
187
Replies
3
Views
97
Replies
4
Views
373
Replies
0
Views
71

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

### 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.

### Which adblocker are you using?

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

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