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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,508
Office Version
  1. 365
Platform
  1. Windows
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
 

Milano1000

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

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

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
48,508
Office Version
  1. 365
Platform
  1. Windows
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),"")
 
Solution

Milano1000

New Member
Joined
Jan 31, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
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:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,508
Office Version
  1. 365
Platform
  1. Windows
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,678
Messages
5,637,741
Members
416,981
Latest member
PLonchar

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
Top