Monster formula to combine data from several columns

[siktir23]

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 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...

 

[siktir23]

Ok, it says I cannot post attachments. What do I need to do to post attachments?

 

[CWatts]

There are two options. Since you're using formula's I'll make the assumption that you're more familair with that process. To keep things from getting too overwhelming.

I'll use a simple example that may help.

Excel Workbook
	A	B	C	D	E
1	First	Middle	Last	Combined	Combined
2	John	Edward	Doe	John Edward Doe	John Edward Doe
Sheet1

After you get the piece of the formula you want working in D2, click on E2, open your name manager. It varies by excel version but CTRL+F3 will open it. Make a new one, call it Name and then paste the formula in.

You'll see in E2, I used =Name which makes the formula much smaller and will be easier for you to manipulate and troubleshoot.

The only cavieat is that using this method is highly dependant on your syntax and what cell is selected when you enter the name. If you use relative references to cells in your name (like A2 B3 etc), you'll run into problems. Using absolute references like I did ($A2 and $B3 means you can use the formula anywhere in that row and it will always pull from that row, but if you go down a row, the row reference will change.

Hope that gives you something to start with!

 

[siktir23]

Thank you for the reply CWatts... yeah, I'm with you on the concept of the formula. However, I'm dealing with a formula like this:

=IF(B4="NSR","NSR",IF(B4="","NSR",IF(B4="EC (NSR)","NSR",IF(B4="ECR (NSR)","NSR",IF(A4=A5,IF(D4<>0,IF(D5<>0,CONCATENATE(G4," ",D4,", ",G5," ",D5)),G4&" "&D4),IF(H4="Above","",G4&" "&D4))))))

...like I said, it's a "monster" already.

The formula above works "almost" like I need it to but I need to add several more layers to combine data from different production runs that affect series of parts and combining the affected serial numbers in one cell of a report.

If I could attach a file it would make a TON more sense, I'm sure. Can I request the permission to attach files from somewhere? Does the abillity become available after a certain number of posts?

Thanks for any advice!

 

[CWatts]

Well, just looking at your B4 test, it looks like you're looking for anything that contains "NSR". You can use this to test if NSR is in the cell:

=NOT(ISERROR(FIND("NSR",B2,1)))

And you could shorten that by using the naming and calling it NSRTest or something so your formula would go down to =NSRTest

Without knowing what your ultimate vision is with this, or what your data looks like, i'm just kind of running blind.

 

[siktir23]

Thanks again for the reply. I'll look into the NAMES. Yes, I completely understand it's tough to offer suggestions without seeing a file. It is exactly why I'm trying to find out how I can attach a file? Do I need to email admin and ask for permission?

 

[siktir23]

n

 

[CWatts]

I'm pretty sure all file attachments are disabled You can use something like Excel Jeanie HTML (http://www.excel-jeanie-html.de/index.php?f=1) which allows you to do stuff like this just by highlighitng your excel ranges:

Data

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:81px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>People</td><td style="text-align:center; ">Wage</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>Bob</td><td style="text-align:center; ">$15.75</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>Sally</td><td style="text-align:center; ">$19.35</td></tr></tbody></table>

 

[Claymationator]

Without seeing your actual workbook to get a better idea it is hard to give any specific insights. However, on another note you could simplify your formula a little by writing it as:

=IF(OR(B4="",B4="NSR",B4="EC (NSR)",B4="ECR (NSR)"),"NSR",IF(A4=A5,IF(D4<>0,IF(D5<>0,G4&" "&D4&", "&G5&" "&D5),G4&" "&D4),IF(H4="Above","",G4&" "&D4)))

If you are able to use an addin to show a summary of your workbook, I am sure that we would be able to get you pointed in the right direction.

 

[siktir23]

Thanks for the help on this folks. I moved the post over to ozgrid.com so I could post the file attachment. Wasn't allowed to install excel jeanie on my work computer...

http://www.ozgrid.com/forum/showthread.php?t=152056&p=548346#post548346

Thanks!