Condense Data

[jomili]

I'm running expense data, and there's a lot of detail to it. Every day I open 27 expense detail files, and in each one there's a pivot from which I pull my data. I copy all of this data, and run through various exercises to boil it down. I'm trying to make the data more palateable to the managment team, and so want to condense/remove unwanted lines. The attached document has a small sample of the data I'm looking at. I've provided 2 views: original format, which is how I originally see the data (the Pivot details), and Consolidated, the data once I've done all of my whitling down.

The part I want to condense has to do with the Requistions, which show in the Consolidated sheet Label column as an A or Z followed by 9 digits, or on the Original sheet in the REQ_ID column (column AL). What I'd like to do is combine all of the line item Descriptions for one Req into the same cell, keep that line and delete the others. But I don't know how to begin doing it, nor whether it would be better to do it in the Original, or in the Consolidated.

I'd appreciate any help I can get on getting this one started.

Thanks,
John

[joao.amancio87]

Hello, do you know SQL? Attached is an example of using SQL Instruction in Workbooks (Excel 2003). Give it a try.

Book1.xls

[jomili]

No, I don't know SQL, and for some reason my security settings today won't let me download the attached workbook. Strange.

Can you post the code and explain?

[joao.amancio87]

Okay, no problem. First, I created a new sheet named "consolidated_eg" and also I renamed the sheet "Original format" to "Original" only. Please, do that things.

Sub UsingSQL()

Dim cn As Object 'ADODB.Connection
Dim cnString As String
Dim rs As Object 'ADODB.Recordset
Dim sqlString As String

Set cn = CreateObject("ADODB.Connection")

cnString = "Provider=Microsoft.Jet.OLEDB.4.0;"
cnString = cnString & "Data Source=" & ThisWorkbook.FullName & ";"
cnString = cnString & "Extended Properties=Excel 8.0;"


sqlString = "SELECT "
sqlString = sqlString & " (DEPTID & Team) AS DEPTID2 "
sqlString = sqlString & ", (DEPTID & MOS2) AS PROGRAM_CODE "
sqlString = sqlString & " FROM [Original$]"


cn.ConnectionString = cnString
cn.Open


Set rs = CreateObject("ADODB.Recordset")
rs.Open sqlString, cn, 1, 1


ThisWorkbook.Worksheets("consolidated_eg").Range("A1").CopyFromRecordset rs


rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

End Sub

Take a look at the variable "sqlString". Its content is the SQL Instruction.

What does means the instruction "SELECT (DEPTID & Team) AS DEPTID2, , (DEPTID & MOS2) AS PROGRAM_CODE FROM [Original$]":

1. Concatenate the columns "DEPTID" and "Team" from the worksheet [Original$] and return them giving them an alias "DEPTID2";
2. Next, return the columns "DEPTID" and "MOS2" from the worksheet [Original$] and return them giving them an alias "PROGRAM_CODE";

Pay attention at the comma ",". It separates the columns. If you miss it, you will receive an run-time error;

The column name at the SQL instruction is the same column name at the worksheet "Original" (in your workbook it is "Original format").

So, if you want to add another columns just follow as is in my code:

e.g.

sqlString = "SELECT "
sqlString = sqlString & " (DEPTID & Team) AS DEPTID2 "
sqlString = sqlString & ", (DEPTID & MOS2) AS PROGRAM_CODE "
sqlString = sqlString & ", MOP"
sqlString = sqlString & ", INVOICE_ID"
sqlString = sqlString & " FROM [Original$]"

And finally, the instruction below is copying the result to a new worksheet:

ThisWorkbook.Worksheets("consolidated_eg").Range("A1").CopyFromRecordset rs

In your case, it could be the "consolidated" worksheet.

I recommend you to learn just the basic of SQL instruction. I'm sure that it will help you so much. Give it a try and reply me.

[jomili]

Joao,

I think you may be right that I need to learn SQL. But so far I'm not seeing where your code is helping. What it looks like it's doing is consolidting the values from different columns, and copying them to the "consolidated_eg" sheet. That's not what I want to do here.

What I want to do is like this:

Before:

REQ_ID	         DESCR
A110075336	Notary Book
A110075336	#232710   Notary Book
A110075336	Notary Stamp
A110075336	Notary Stamp

After:

REQ_ID	          DESCR
A110075336	"Notary Book
                   #232710   Notary Book
                   Notary Stamp
                   Notary Stamp"

or like this:

REQ_ID	DESCR
A110075336	Notary Book, #232710   Notary Book, Notary Stamp, Notary Stamp

In other words, take all the lines for one Req and combine the DESCR fields into one line (whoops; I also would need to combine the MONETARY_AMOUNT lines, by adding them together for that one REQ_ID).

[joao.amancio87]

Is the REQ_ID field unique? Does it cannot be repeated? In other words, do you want to group your database by REQ_ID and DESCR fields?

[jomili]

Okay, this may get complicated (it gets worse as I think about it):

The ReqID is a unique value, for a document containing one to many lines.
We want to combine those lines.

Going to the Original sheet:
We need every column.
In the REQ_ID field, IF a value exists, we want to group like items (ReqIDs) on the same line, so in MOST cases we would have unique ReqIDs on each line. This means combining all of the DESCRs for that ReqID into one cell in the DESCR column, and adding all of the amounts in the MONETARY_AMOUNT (column G) for that ReqID into one in the MONETARY_AMOUNT column.

Exceptions are: IF DataType (column F) isn't consistent, there might be more than one line for that ReqID. Sorry.

I'm about to head to a meeting, so can't reply again for about an hour.

[jomili]

I just realized I left this one hanging. I sidestepped the whole issue by pulling in the description field for each Req instead of the line item description for each line in the req. That consolidated it down just fine. Sorry if I left anybody hanging on this one.