How to insert variable number of columns based on cell value

October 12, 2017 at 08:32:07
Specs: Windows 10
Hi, I hope someone can help me with this.

I have an Excel sheet that contains detailed shipments information. The problem is that in column H there is the Purchase Order # reference, and often there is more than one Purchase Order # in the cell (values delimited by space, semicolon, slash, comma and whatever the user would choose).

I need to have only one PO # per line, so essentially based on a number of PO #s in the cell of column H - create the corresponding number of lines (if the number of PO references is >1) right underneath with information from other columns copied into these lines. So having unique values in column H with same data from other columns.

I know there is no formula way to perform this operation in Excel (I have Excel 2016 32 bit) but could you please help me with Macro?

I can send you the file if needed.

Thanks in advance,

Max


See More: How to insert variable number of columns based on cell value

Reply ↓  Report •

#1
October 12, 2017 at 19:58:51
I'm a bit confused. Your subject line says "insert variable number of columns" but the text of your question says "create the corresponding number of lines". I'll assume that the use of the word "columns" in the subject line is incorrect and that by "create lines" you mean "create rows".

With that assumption made, the following condition makes writing a macro a bit difficult:

delimited by space, semicolon, slash, comma and whatever the user would choose

Unless the code knows how to recognize the actual PO#'s, it won't be able to determine how many rows to insert. Theoretically, the code could look for spaces, then look for semicolons, then look for slashes then look for commas, but it won't be able to look for "whatever the user would choose". It has to know what to look for.

Since the delimiters are not going to be consistent, perhaps there is something about the PO#'s themselves that the code could look for? Could you post a few examples of what a cell with multiple PO#'s might look like?

Better yet, could you do something in the spreadsheet itself that would force the users to use a specified delimiter? Leaving it up to the users to enter "whatever they choose" and then expecting a macro to figure it out is just asking for trouble. VBA code is not magic. It's nothing more than a set of instructions that get executed one line of code at a time. It can't think and it can't make its own decisions. It can only do exactly what you tell it to do.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
October 13, 2017 at 09:11:57
Hi DerbyDad03,

Thanks for your kind response. You're right, the title is wrong, I need the creation of rows depending on a number of PO# references in a cell.

Re the delimiters, as the data is obtained from the ERP system, there is a number of typical delimiters that people use to enter the data like space, semicolon, slash or comma. The complexity is that sometimes users can use a combination of delimiters (particularly double space) or come up with their own delimiter.

Here are some examples of the input data:

024356;024404
POA002051;POA002062
POA002083 / POA002084
024363:024408
POA001998;POA001969;POA001976;POA001970;POA001997
024372,024373,024374
024381 024383 024386

As you see from different PO references used above, the problem is that they could be of various formats, like just numbers, letters+numbers, PO+number, POA+number, etc. depending on different ERP systems and settings that I guess makes identification of unique instances problematic.

However, there is a way to educate those who enter the data in ERP to use standard set of delimiters. Will this help to make the case solvable?

Thanks in advance,

Max




Reply ↓  Report •

#3
October 13, 2017 at 10:27:56
re: However, there is a way to educate those who enter the data in ERP to use standard set of delimiters. Will this help to make the case solvable?

Well, it sure will make it easier.

For example, the following code is one method of counting the number of PO's in each cell. It basically searches each cell in the range for the characters that you showed in your example. I'll tell you right up front that there are multiple ways to accomplish that goal. All I'm doing is supplying one method as a means to show the concept of what would need to be done assuming we know exactly which set of delimiters will be used. Later I will also explain why this code will not work for all of the example data that you posted. I do this as a means to show you why you can't just let your users enter data in any format that they choose with the expectation that VBA will be able to figure it out.

Sub countPO()
 For Each cell In Sheets(1).Range("H1:H7")
'Search for ;
   If Len(cell) - Len(Replace(cell, ";", "")) > 0 Then
    numPO = Len(cell) - Len(Replace(cell, ";", "")) + 1
     Range(cell.Offset(0, 1).Address) = numPO
   End If
'Search for /
   If Len(cell) - Len(Replace(cell, "/", "")) > 0 Then
     numPO = Len(cell) - Len(Replace(cell, "/", "")) + 1
      Range(cell.Offset(0, 1).Address) = numPO
   End If
'Search for :
   If Len(cell) - Len(Replace(cell, ":", "")) > 0 Then
    numPO = Len(cell) - Len(Replace(cell, ":", "")) + 1
     Range(cell.Offset(0, 1).Address) = numPO
   End If
'Search for ,
   If Len(cell) - Len(Replace(cell, ",", "")) > 0 Then
    numPO = Len(cell) - Len(Replace(cell, ",", "")) + 1
     Range(cell.Offset(0, 1).Address) = numPO
   End If
'Search for Space
   If Len(cell) - Len(Replace(cell, " ", "")) > 0 Then
    numPO = Len(cell) - Len(Replace(cell, " ", "")) + 1
     Range(cell.Offset(0, 1).Address) = numPO
   End If
 Next
End Sub

That code will work for 6 of the 7 examples that you posted. It will not work for this example:

POA002083 / POA002084

That example consists of multiple delimiters, not just 1: space slash space Since the code is searching for spaces separately from slashes, the order of the searches will impact the result. When it searches for a slash, it will find 1 and calculate that there are 2 PO's. Then when it search for spaces, it will find 2 and calculate that there are 3 PO's. The point here is that it would be very difficult to write code that could account for each and every possible combination of delimiters that your users might choose.

If you force your users to use a consistent format, e.g. colons with no spaces or slashes with 2 spaces or semi-colons with one space, etc. then the code could look for that exact combination within a single instruction and determine the number of PO's in each cell.

Bottom line: VBA does not deal with inconsistencies very well and it does not deal with unknowns at all.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
October 13, 2017 at 11:23:21
Thank you so much for this!

I understand the issue with multiple delimiters, so will push hard on using delimiters from the defined list. Wondering if the macro written above would work fine if in the same cell different delimiters are used (e.g. POA0001714;POA001683/POA001715)?

I tried running the script above (I'm far from being a pro as you may see) and it didn't insert rows but returned a number of POs in the cell nearby -

POA0001714;POA001683;POA001715;POA001710;POA001712;POA001711;POA001682 7

Also, wondering if after inserting the new rows the macro will be able to place unique PO #s in the column H and paste the data from other columns in the source row into new rows (so that from the example above I'd derive 7 lines with different PO #'s with everything else the same)?

Thanks a ton!

Max


Reply ↓  Report •

Ask Question