Collating data from separate columns

Milano1000

New Member
Joined
Jan 31, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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 :unsure:

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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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),"")
 
Upvote 0
Solution
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.
 
Upvote 0
what about

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:
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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