# Collating data from separate columns

#### Milano1000

##### New Member
Hello all, I am confused how to collate or tabulate the results in this data set. Any help would be appreciated. I think this can be accomplished by using a formula but every effort has failed. I have given up and am asking for help.

This is it is just a set of math mathematical formulas that represent data points for a roll playing game. There 3 columns, Range Band (column B), Damage (column C), and Penetration (column D). The columns C and D are two separate formulas drawn from mathematical formulas. What I am trying to accomplish is take the data set of B:3 to D:31 and have Excel automatically Create the table in cells F:20 to K:21. Could I get help doing this from you all?

To clarify or reiterate, Damage gets smaller with Range, as does the Penetration. I am looking for a way to tabulate the data in table B:3 to D:31. Then reproduce it in F:20 to K:21. I highlighted the applicable data in red (column B) and Orange (column C and D). For example, (in row 7) at 145m the data should be eventually read as "4 / 2". As in a reference to G:20 and G:21. At 900m (row 27) should eventually read "1 / nil" as in K:20 and K:21.

I hope what I am asking makes sense. If not please do not hesitate to ask me and I will try to reiterate what I am trying to do.

Formula results.xlsx
ABCDEFGHIJKL
1
2BandDamagePenetration
30
4052
5542
61042
714542
815042
915542
1016042
1116542
1217032
1317532
1422532
1525032
1627532
1730033
1832533
1935033
20375230 - 55 - 165165 - 275275 - 350350 - 575575 - 950
21400235 / 24 / 23 / 23 / nil2 / nil1 / nil
2252524
2355024
2457524
2560015
2662515
27900112
28925113
29950015
30975016
311000018
32
Sheet1

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

It is not entirely clear to me.

Damage gets smaller with Range, as does the Penetration.
Your data shows Penetration getting larger, not smaller

You refer to 145m and 900m. There are no "m"s shown so what is that all about?

Can you explain in words how you manually get the results shown in, say, F21, H21 and K21

#### Milano1000

##### New Member
I apologize You are correct the Penetration IS getting larger, and you are correct there is no "m" within the B column. That is my oversight.

How I came to the table in F:20 to K:21 is I reference any change in the values in columns C and D. Thus, there is a change from 5 to 4 in column C at B:5. So that is how I came up with F:21. Between 0 - 5 the Damage is 5 and Penetration is 2. Short hand to "5 / 2".

H:21 is done the same way. (Although I see that I highlighted the wrong row. I should have highlighted B:12 at 170. Not B:11.) Anyway, looking down column C the Damage changes from 4 to 3 at 170 meters. Thus at Ranges of 170 to 300 the damage is 3. ALSO, or in addition, there is no change in Penetration within those ranges. ie. Cells C:12 to D:16 there is no change in either Damage or Pen. Cells H:20 and H:21 represent this data in a more easily digestible format by saying that from 170 - 300 the Damage is 3 and the Penetration is 2.

K:21 is similar in all respects. I messed up the highlighting again. I should have highlighted, B:25. Also Penetration when it becomes greater than the Damage it cannot penetrate thus the Pen is "nil". From ranges of 600 to 950 the damage is 1 and the pen is nil. Cell K:21 shows this as "1 / nil".

Notice also that at row 17, or 300 the Damage remains 3 but the penetration rises to 3 as well so, that data is reflected in I:20 to I:21. At row 20 there is another change in data, this time in the C column. The Dam went from 3 to 2. Thus the change in data representation in cells J:20 and J:21.

I hope this helps and below I fixed my mistakes and are shown below. Thank you for the help and I hope I explained it well enough.

Formula results.xlsx
ABCDEFGHIJKL
1
2BandDamagePenetration
30
4052
5542
61042
714542
815042
915542
1016042
1116542
1217032
1317532
1422532
1525032
1627532
1730033
1832533
1935033
20375230 - 55 - 170170 - 300300 - 375375 - 600600 - 950
21400235 / 24 / 23 / 23 / nil2 / nil1 / nil
2252524
2355024
2457524
2560015
2662515
27900112
28925113
29950015
30975016
311000018
32
Sheet1

#### aRandomHelper

##### Board Regular
I suck at using formula, so try this VBA solution:

VBA Code:
``````Sub me1160201_calc()
Dim i As Long, d, cur, s, c As Long

Application.ScreenUpdating = False
Set d = CreateObject("Scripting.dictionary")
c = 5
For i = 3 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "C").Value & Cells(i, "C").Value <> "" Then
s = Cells(i, "C").Value & " / " & IIf(Cells(i, "D").Value < Cells(i, "C").Value, Cells(i, "D").Value, "nil")
If Not d.exists(s) Then
cur = Cells(i, "B").Value
d.Item(s) = Empty
c = c + 1
Cells(20, c).Resize(2).Value = Application.Transpose(Array(cur & " - " & Cells(i + 1, "B").Value, s))
Else
Cells(20, c).Resize(2).Value = Application.Transpose(Array(cur & " - " & Cells(i + 1, "B").Value, s))
End If
End If
If i = Cells(Rows.Count, "B").End(xlUp).Row Then
Cells(20, c).Resize(2).Value = Application.Transpose(Array(cur & " - " & Cells(i, "B").Value, s))
End If
Next
Application.ScreenUpdating = True
End Sub``````

Your data must start from B3 like shown in your screenshot. Output will be placed from F20 onwards.

#### Milano1000

##### New Member

Oh wow. Ok thank you very much. I have not even heard of a VBA code. (heck, I'm pretty naive with Excel!) I'll take a look at how to go about doing that. Thank you very much.

#### Peter_SSs

##### MrExcel MVP, Moderator
For a formula approach, I used some helper columns.

Milano1000.xlsm
ABCDEFGHIJKLMNOP
1
2BandDamagePenetration
30
40520
554210 - 55 / 2
61042
714542
815042
915542
1016042
1116542
121703225 - 1704 / 2
1317532
1422532
1525032
1627532
17300333170 - 3003 / 2
1832533
1935033
20375234300 - 3753 / nil0 - 55 - 170170 - 300300 - 375375 - 600600 - 950950 - 1000
2140023   5 / 24 / 23 / 23 / nil2 / nil1 / nil0 / nil
2252524
2355024
2457524
25600155375 - 6002 / nil
2662515
27900112
28925113
299500156600 - 9501 / nil
30975016
311000018
327950 - 10000 / nil
33
Sheet1
Cell Formulas
RangeFormula
E5:E32E5=IF(OR(C5<C4,AND(D5>D4,D5<=C5),C5=""),LOOKUP(10^7,E\$4:E4)+1,"")
F5:F32F5=IF(E5="","",LOOKUP(9^9,E\$4:E4,B\$4:B4)&" - "&IF(B5="",B4,B5))
G5:G32G5=IF(E5="","",C4&" / "&IF(D4<C4,D4,"nil"))
I20:P20I20=IFNA(VLOOKUP(COLUMNS(\$I:I),\$E\$4:\$F\$32,2,0),"")
I21:P21I21=IFNA(VLOOKUP(COLUMNS(\$I:I),\$E\$4:\$G\$32,3,0),"")

#### Milano1000

##### New Member

WOW! Firstly I want to say thank you to you all. I have posted this problem on other forums and you two are the first to offer true help. I sincerely thank you. You've given me options and a direction to be able to help myself. Thank you again.

I will work on this for the next few days and will hopefully come back and push the "Marked as Solution" button.

#### Dossfm0q

##### Banned User

Collating data from separate columns.xlsx
ABCDEFGHIJKLMNOP
1
2BandDamagePenetrationif needed or delete
30
40521
554220 - 5 5 / 2
61042
714542
815042
915542
1016042
1116542
121703235 - 170 4 / 2
1317532
1422532
1525032
1627532
17300334170 - 300 3 / 2
1832533
1935033
20375235300 - 3753 / nil0 - 55 - 170170 - 300300 - 375375 - 600600 - 950950 -1000
2140023    5 / 2 4 / 2 3 / 23 / nil2 / nil1 / nil0 / nil
2252524
2355024
2457524
25600156375 - 6002 / nil
2662515
27900112
28925113
299500157600 - 9501 / nil
30975016
311000018 950 -10000 / nil
Sheet1
Cell Formulas
RangeFormula
F4:F31F4=IFERROR(AGGREGATE(15,6,(COLUMN(\$J\$20:\$P20)-COLUMN(\$J\$20)+1)/(\$B4=(--MID(\$J\$20:\$P20,1,FIND("-",\$J\$20:\$P20)-1))),1),"")
G4:G31G4=IFERROR(INDEX(\$J\$20:\$P20,1,AGGREGATE(15,6,((COLUMN(\$J\$20:\$P20)-COLUMN(\$J\$20)+1)/(\$B4=(--MID(\$J\$20:\$P20,FIND("-",\$J\$20:\$P20)+1,LEN(\$J\$20:\$P20)-FIND("-",\$J\$20:\$P20))))),1)),"")
H4:H31H4=IFERROR(INDEX(\$J\$21:\$P\$21,1,AGGREGATE(15,6,((COLUMN(\$J\$20:\$P20)-COLUMN(\$J\$20)+1)/(\$B4=(--MID(\$J\$20:\$P20,FIND("-",\$J\$20:\$P20)+1,LEN(\$J\$20:\$P20)-FIND("-",\$J\$20:\$P20))))),1)),"")

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
Wondering if you read what the OP is trying to achieve?
What I am trying to accomplish is take the data set of B:3 to D:31 and have Excel automatically Create the table in cells F:20 to K:21.

#### Peter_SSs

##### MrExcel MVP, Moderator
WOW! Firstly I want to say thank you to you all. I have posted this problem on other forums and you two are the first to offer true help. I sincerely thank you. You've given me options and a direction to be able to help myself. Thank you again.

I will work on this for the next few days and will hopefully come back and push the "Marked as Solution" button.
You are very welcome. We look forward to hearing how it goes for you.

Replies
1
Views
155
Replies
0
Views
70
Replies
3
Views
45
Replies
4
Views
202
Replies
4
Views
369

1,127,539
Messages
5,625,404
Members
416,100
Latest member
lirongr1996

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