Use formula to combine data from several columns

Hi all,

I have a fairly complex spreadsheet (5k+ rows and 25+ columns) where I am trying to combine data from several different columns into a simpler format in order to populate a summary report I have to submit each month.

I've come up with a HUGE formula full of IF statments to try and combine the columns if certain criteria are met. I've gotten the single and double line entry formulas working correctly but when I try to combine data from several different rows, the formula becomes too much to manage.

I'm looking for suggestions on how to combine the data more efficiently. I'm open to any method that would work... VBA, adding more columns, formulas... anything.

I've created a very simplified example file with only the columns that affect the formulas I'm trying to figure out.

In the attached example, I need to combine data from orders from different companies. I need to combine model and serial numbers affected by the purchases based on the models ordered and the serial numbers that incorporated the changes from the production line (ie being able to tell the customer "the specific serial numbers that incorporate your change are x, y, z").

The sheet "Example 1" will not be seen by anyone else in the company so I can manipulate the data any way I need to. The sheet "Summary Report" is the format in which I need to display the data and the format is pretty much set in stone.

Thank you in advance for your thoughts and please let me know if you have any questions...

Re: Use formula to combine data from several columns

AAE,

Thank you for correcting the subject on my post. I will pay more attention to my verbiage next time.

I'm not quite sure what you were referencing in my 5k+ rows and 25+ columns sentence...

Quote from AAE;548361

Must be a new Excel version - my Excel 2007 only has 16,384 columns. :wink:

Did you think I was saying 25,000 columns instead of 25+ columns? :wink:

Re: Use formula to combine data from several columns

Herbds7, thank you for the work on your file! Sorry it's taken a couple of days for me to get back to you. I REALLY like the concept you offered and I understand the concept but I'm not quite good enough to put everything I need together. Seems by adding a couple more columns, I've jiggered up the way the macro pulls the columns and I haven't been able to get it tweaked back to where it shows the model number in front of the serial numbers.

I've added a little more information to your file and re-uploaded. If you could take a look I would GREATLY appreciate it!

I've created a new tab that shows how I need the final report to look.

Thanks in advance!

(Rename file to ".xlsm")

Re: Use formula to combine data from several columns

Wow Herbds7.... impressive. VERY impressive. Thank you soooooooooo much for your help!

Re: Use formula to combine data from several columns

Herbds7,

Once again, thank you for working with my file. I really do appreciate it.

I want to be able to understand the code you've written so I have an opportunity to fine tune it myself to do exactly what I need it to do rather than just asking someone to write it for me. I pick up pretty quick but I'm completely self-taught so I might not understand some of the stuff right off the bat.

I would like to add the letters NSR to the Model/Serial column on the Summary sheet IF the "Type" column on the "data" sheet is blank OR if it has any combination of "NSR" (stands for no serial required) in it. The code can enter "NSR" and move on to the next row if that is the case instead of calculating the actual serial numbers.

Looking at module 2, I would think there could be an "if" statement right above:

For n = 4 To 6
        With .ListRows(m).Range
            If .Cells(n) <> 0 And .Cells(5) <> 0 Then
                a(i, 1) = a(i, 1) & sp(n - 3) & .Cells(n)
            End If
        End With
        Next n

but I don't understand enough about how exactly that code calculates the cell to be able to add the NSR statement above it. I get the n = 4 To 6 part but the NSR is located in Cell(3) so I'm not quite sure how to incorporate that into the code.

Any insight would be greatly appreciated!

Thanks!

Re: Use formula to combine data from several columns

Well, after much trial and error, I was able to come up with a little bit better of an option. Seemed the "Type" column with the "NSR" text was a little unreliable. It made more sense to focus on whether or not the row had any start/stop serial numbers.

Still not sure exactly how the code works, but figured it out well enough to get:

For n = 4 To 6
        With .ListRows(m).Range
            If .Cells(n) = 0 And .Cells(5) = 0 Then
                a(i, 1) = "  NSR"
            ElseIf .Cells(n) <> 0 And .Cells(5) <> 0 Then
                a(i, 1) = a(i, 1) & sp(n - 3) & .Cells(n)
            End If
        End With
        Next n

to work...

Re: Use formula to combine data from several columns

Herbds7 (or anyone else that might be able to help),

I was wondering if you could nudge me in the right direction on something?

I have copied the code to recreate more data in the same file with a few different colums of information. I've copied the "Summary" sheet, renaming to "Summary2". I've copied "Table1" and renamed to "Table7" and included the different columns I need to have in the new summary.

I keep getting an error at the beginning of the macro. The following code from the original macro works fine:

Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:=Range("[COLOR=red]Table1[/COLOR][#All]"), _
            Version:=xlPivotTableVersion14)

but I get a "Run-Time error '13': Type mismatch" when I change the SourceData range to the new table:

Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:=Range("[COLOR=red]Table7[/COLOR][#All]"), _
            Version:=xlPivotTableVersion14)

Any ideas as to why I'm getting the error when I've copied and renamed everything from the original code/tables?

Thanks for any input.

Re: Use formula to combine data from several columns

One of the extra columns I added was a "Comments" column and some of the comments had more than 255 characters. That is what was throwing the error.