Combining data in columns into one row.....

[Philody]

Hello,

First let me apologize for using a basic description as I am not an Excel power user. I am trying to combine column of "years" into a single row, for example:

Product Position Make Model Year Year End
MPN Back Make Model 1997
MPN Back Make Model 1998
MPN Back Make Model 1999
MPN Back Make Model 2000
MPN Back Make Model 2001
MPN Back Make Model 2002

I would like the formula to return:

Product Position Make Model Year Year End
MPN Back Make Model 1997 2002
MPN Back Make Model 1998
MPN Back Make Model 1999
MPN Back Make Model 2000
MPN Back Make Model 2001
MPN Back Make Model 2002

The other scenario is that there maybe times when years in the middle are skipped so the formula would need to "reset" itself as the 2000 year is "missing:"
Product Position Make Model Year Year End
MPN Back Make Model 1997 1999
MPN Back Make Model 1998
MPN Back Make Model 1999
MPN Back Make Model 2001 2002
MPN Back Make Model 2002

Basically I need to now that a part number fits 1997-2002 in one instance and 1997-1999 and 2001-2002 in a second instance.

Attached is the sample excel file, your help would be greatly appreciated.
Philip

[arlu1201]

Should only 1 year-end value be entered for each group of data? And the remaining rows be left blank?

[Philody]

Hi Arlu1201,

Yes, it should only take into account the minimum and maximum values. So for example I would like to know that the part fits 1997 - 2002, but ignores the rest of the years.

Thanks for your reply,
Philip

[arlu1201]

Does your original data also have a blank row between each data set? I am asking you this, so that you do not face issues while using your macro on your original file.

[Philody]

Hello,

You are correct there are no blank rows, this was for an example to separate and illustrate that the data may vary and have breaks in years.

Thanks,
Philip

[arlu1201]

If there is no blank row in between the data, which field can i use to identify the different groups?

[Philody]

Hello,

Attached is the actual sample, I am not sure if it will help more.

Sample.xls

Thanks,
Philip

[arlu1201]

After seeing your original snapshot, i would say - you should have uploaded a sample (the 1st time) that looks exactly like this. In this file, you have data in alternate columns but its not that way in the sample file that you uploaded. If i had to provide you a macro, you might have faced issues in replicating it in your original file.

Is column K already populated in your file or do you want the macro to do it? This will be an important column to make distinctions between rows.

[Philody]

Arlette,

Thank you, I am new to the forum and in the future will upload a sample data set as you suggest. I have attached a "Sample Results" to show the desired end results with corresponding year spans. If possible it would be desirable to delete the rows that are not "used" in the combination and separate the years with a dash.



Thank you,
Philip

[arlu1201]

So the rows which are in between the start year and end year should be deleted?

[Philody]

Preferably they would be deleted, but if it is not easily accomplished I maybe able to somehow sort and manually delete them.

[arlu1201]

Try this code - Ensure that there is data only upto column G. The macro will populate columns I, J and K.

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[Philody]

Arlette,

Thank you for providing the VBA code. It seems that when I run it, it deletes everything? Attached is the sample with the code, am i doing something wrong?

Thanks,
Philip

[Philody]

Sorry, here is the attached file..

Sample 3.xls

[arlu1201]

The earlier file that you uploaded had blank columns in between the data, this one doesnt. Hence, the macro does not work properly.

Should i change the macro to match this file?

[Philody]

Arlette,

I see, I was a little confused when you stated that there should be no Data from colum G on. I tried it again with spaces and it worked perfectly. Thank you so much for all your help and patience in helping to solve this. It has been a huge help.

Philip

[arlu1201]

Yeah, it was tricky for me as well, but i loved it.

[Philody]

Arlette,

We have been using this VB script for a bit and just realized that we are seeing two errors occur regularly. Attached is the file along with the VB script provided and the output we see when we run it.

Bulb Data - VB Review 6 Output.xlsmBulb Data - VB Review 6.xlsm

Error 1:
The output results in this:
T10 4 1210SMD Tail Light Buick Lucerne 2007-1991

However, that is not the correct ending year. It should look like this:
T10 4 1210SMD Tail Light Buick Lucerne 2007-2011

This vehicle was not produced in 1991 and the formula is pulling that number from somewhere else.

Error 2:
The output results in this:
T15 9LED Back Up / Reverse Light Buick LaCrosse 2005-2010
T15 9LED Back Up / Reverse Light Buick LaCrosse 2012-2013

The output result should be:
T15 9LED Back Up / Reverse Light Buick LaCrosse 2005-2013

For some reason it is dropping off the 2011 and then separating it as two separate products when it should be one.

I am not sure if the +1 -1 in the script is affected by the last digit year and creates the same errors for 2001 / 2011. Your help is appreciated.

I just marked this as "unsolved."

Thank you,
Philip