Splitting one row into multiple rows using cell value

[newbiebio78]

Hello, I am new to this forum AND new to coding in Excel.

I need to split data from one row into multiple rows based on the number in that row AND simultaneously assign a new value to another column based on another cell. Here's what I mean:

AD young
2 10
2 7
2 8
2 10
1 5
2 5


I want to split the AD column such that each AD (adult female) has her own separate column and is assigned half the number of young. It would look like this:

AD young
1 5
1 5
1 3.5
1 3.5
1 4
1 4
1 5
1 5
1 5
1 2.5
1 2.5

PLEASE HELP ME! I can't find suitable code that deals with this specific problem, nor do I know enough about VBA to alter codes written to split text in cells separated by delimiters (which is not my issue).

 

[newbiebio78]

To clarify; I want each AD to have her own ROW, not column. Apologies for the typo.

 

[Rick Rothstein]

I believe this macro will do what you want...

[table="width: 500"]
[tr]
	[td]Sub SplitValuesDown()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B2").Resize(Application.Sum(Range("A2:A" & LastRow))) = Application.Transpose(Split(Join(Application.Transpose(Evaluate(Replace("IF(A2:A#="""","""",REPT(B2:B#/A2:A#&""|"",A2:A#))", "#", LastRow))), ""), "|"))
  Range("A2:A" & LastRow).Resize(Application.Sum(Range("A2:A" & LastRow))) = 1
End Sub[/td]
[/tr]
[/table]

 

[lrobbo314]

This is an amazing solution Rick.

Can you explain what is going on in this line of code?

Application.Transpose(Split(Join(Application.Transpose(Evaluate(Replace("IF(A2:A#="""","""",REPT(B2:B#/A2:A#&""|"",A2:A#))", "#", LastRow))), ""), "|"))

I've never seen the "#" being used in range references like that. Also, that whole line of code is just crazy.

 

[newbiebio78]

Hmm, I can't get this code to run properly. I get a run-time error '13' message, "type mismatch". When I hit "debug", it highlights the "Range(B2)...." line.

Did I mention that I have no idea what I'm doing? Also, does it matter that I have many cases with just 1 AD (so no splitting required) and I have many cases with values > 2.

Thanks for your help so far!

 

[Rick Rothstein]

Hmm, I can't get this code to run properly. I get a run-time error '13' message, "type mismatch". When I hit "debug", it highlights the "Range(B2)...." line.

The only way I can get my code to fail with that error message is if the data you posted are not in Columns A and B. Is your "AD" values in Column A and your "young" values in Column B?

 

[newbiebio78]

I had that set up correctly, but it was a no-go for some reason.

Now it is working, but it doesn't not copy all columns in the record. I have additional data in columns C - K that need to be copied too.

Thanks!

 

[Rick Rothstein]

Now it is working, but it doesn't not copy all columns in the record. I have additional data in columns C - K that need to be copied too.

I just checked... this is the first time you have mentioned having more data than what you showed us in Message #1 . I presume the data on each row is or can be different from the other rows and has to be kept together with the values that are split for that row. If so, that means the code I gave you can no longer be use and we will have to write a completely new... and different... macro. I'll be back once I work it out unless, of course, someone gives you a workable solution before then. Until then, I would like you to consider the following generalize comment I post in situations like this...

Please Note
-------------------
For future questions you may ask, please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[newbiebio78]

Hi Rick,

Thank you for your help. My apologies for not including all of those details about my data. I certainly did not mean to waste your time or anyone else's.

I have never posted anything on boards before and wasn't sure what information would be superfluous. Also, I've never used macros before. I will not make these same mistakes in the future. Thank you for your response, your help, and the "please note" information that you provided.

 

[Rick Rothstein]

Thank you for your help. My apologies for not including all of those details about my data. I certainly did not mean to waste your time or anyone else's.

No problem... you are new to this... I understand.

I have never posted anything on boards before and wasn't sure what information would be superfluous.

That is why my "Please Note" starts off with "For future questions you may ask".

As for the code, give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub SplitValuesDown()
  Dim R As Long, C As Long
  For R = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    If Cells(R, 1).Value > 1 Then
      Rows(R + 1).Resize(Cells(R, 1).Value - 1).Insert
      Cells(R, 2).Resize(Cells(R, 1).Value).Value = Cells(R, 2).Value / Cells(R, 1).Value
    End If
  Next
  With Range("A1").CurrentRegion
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub[/td]
[/tr]
[/table]