I am fairly new to excel. I use it to do relatively basic formulas and
charts, but I am not very good with Macros and VB.

I have been asked to help make out reports more readable. Basically we
have a query that dumps a bunch of info into an excel spread sheet.

I then have to sort it and delete some columns we don't need. up untill
this point i am fine making a macro. but after that i get stumped.

basically we have a list of items that were invoiced, so there are rows
that have the same invoice number, we sort them by invoice number so we
can see the group of items on that order. What my boss would like, is
for these separate invoices to be grouped two lines inserted and a
total sales sumed up (which i know how to do),

basically my question is, is there a way to run through a column of
data useing vb and find unique values and then enter a new line and
preform a task.
I am used to doing most of my stuff in php so this is kinda what i was
thinking

loop(runs through each row in a column, or data range b2:b230)
{
$lastnumber=someinvoicenumber //the last unique one
$newnumber=currentCellValue //current cell value
if ($newnumber=$lastnumber) //checks if they match

{ $count=$count+1 //if they match add to the counter

}
else //they don't match, start routine
{ insert two rows
add sum field into a cell on that row,
the sum would go from that cell to that cell - $count
$lastnumber = $newnumber
}
endif;

so basically i don't know how to write that in vb. I hope i explained
my self somewhat okay. Any help would be greatly appreciated!!!!!


--
trav
------------------------------------------------------------------------
trav's Profile: http://www.excelforum.com/member.php...o&userid=31420
View this thread: http://www.excelforum.com/showthread...hreadid=511138

Can I ask some questions?

Talking about "a query" is that MS Query?

Yes: You can do sorting and selection and column deletion in MS Query.
So when the data arrives it is already sorted and filtered.
No: What is it? Is it a .csv file import?

Your task is:

sort all rows by invoice number and item
for every invoice number you want to add two lines and create a total
of all items on the invoice. What are the 2 lines for? Readibility? Is
it 2 lines below the invoice or 1 above and 1 below?

Does that summarize your problem?

Hans

hans,
First off, thanks for the quick reply.

okay a little background, I know php and mysql, so most of the stuff i
do is moving client to server to client.

I now just started working for a company that uses access and great
plains. I have yet to meet with the Database Admin, so i do not know
the table structure or query structure yet.

Once I get that down, i will start writing the queries myself.

untill then i am forced to use the access program that everyone else
has. in there you can pick an open order report and then it will send
it to excel, (i don't know how, like i said i don't work with microsoft
products much). the excel spreadsheet then has about 15 columns with
various data in them for each item

in order to make the report more readable, we sort based on the req
ship date and then by order number.

we also delete some of the columns that we don't needs (this is why i
want to set up my own queries, because you can see how inefficient this
is).

anyway, so I set up a macro that pretty much does that, and adds a
title and date, so we can forward the report to others.

the problem is that there is no break from one group or orders to the
next, so it is hard to see a summary. I have been going through it by
hand and inserting two blank rows in between each group of orders so it
is easier to read. I also add a sum function to get the total sale from
the group of each unique order number.

after doing about 200 records i was fed up and thought there had to be
a way to make a macro do this, but i don't know how to set it up so it
looks for unique value groups and preform a task at the end of the
group.

i hope that clearifies it somewhat.

like i said, if they had php running on the server i could pull up a
webpage that would look nifty, but unfortunately they do not, and i
find VB very confusing, but then again i never really use it.

Thanks for the help!!!
travis


--
trav
------------------------------------------------------------------------
trav's Profile: http://www.excelforum.com/member.php...o&userid=31420
View this thread: http://www.excelforum.com/showthread...hreadid=511138

Hi Travis,

I've tried to come up with a quick solution for what I seem to
understand is your request. Check it and if it is what you want we can
always make it nice and neat.

This is the "query" as you get it:

req ship date orderno. itemno. qty amount
01.05.2006 14510 1234 50 150,00
01.05.2006 14510 2345 70 178,00
01.05.2006 14510 4221 35 98,00
01.05.2006 16422 7552 115 485,00
01.05.2006 16422 6221 78 123,00
01.05.2006 18532 1122 114 253,00
01.05.2006 18532 1422 119 193,00
10.05.2006 16422 1558 20 452,00
10.05.2006 19741 4444 42 79,00
10.05.2006 19741 5555 115 452,00
10.05.2006 21020 1888 30 78,00
22.05.2006 18010 2445 45 70,00
22.05.2006 18010 2446 121 225,00

And this is the result of the proposed procedu

req ship date orderno. itemno. qty amount
01.05.2006 14510 1234 50 150,00
01.05.2006 14510 2345 70 178,00
01.05.2006 14510 4221 35 98,00
Order Total 426,00
01.05.2006 16422 7552 115 485,00
01.05.2006 16422 6221 78 123,00
Order Total 608,00
01.05.2006 18532 1122 114 253,00
01.05.2006 18532 1422 119 193,00
Order Total 446,00
Group Total 1480,00
10.05.2006 16422 1558 20 452,00
Order Total 452,00
10.05.2006 19741 4444 42 79,00
10.05.2006 19741 5555 115 452,00
Order Total 531,00
10.05.2006 21020 1888 30 78,00
Order Total 78,00
Group Total 1061,00
22.05.2006 18010 2445 45 70,00
22.05.2006 18010 2446 121 225,00
Order Total 295,00
Group Total 295,00

And this is the procedure that does this:

sheetname = ActiveSheet.Name
GroupCol = 1 'column that holds the group
OrderCol = 2 'Column that holds the order
ValueCol = 5 ' column to sum
RepStartRow = 2 'row number where the report data starts
RowsEndOfRep = 4 'How many empty define the end of the report
GroupStartCol = 0 'first row of current group
GroupEndCol = 0 'last row of current group
currgroupkey = "" 'key of current group
currorderkey = "" 'key of current order
prevgroupkey = "" 'key of previous group
prevorderkey = "" 'key of previous order
endofreport = False 'signal "end of report"
NumRowsGroup = 0 'number of rows in current group
NumRowsOrder = 0 'number of rows in current order
currentrow = RepStartRow 'current row number
sumorder = 0 'sum of current order
sumgroup = 0 'sum of current group

If prevgroupkey = "" Then
prevgroupkey = Worksheets(sheetname).Cells(RepStartRow,
GroupCol)
prevorderkey = Worksheets(sheetname).Cells(RepStartRow,
OrderCol)
End If
While endofreport = False
Do
If Worksheets(sheetname).Cells(currentrow, GroupCol).Value
= "" And _
Worksheets(sheetname).Cells(currentrow + 1,
GroupCol).Value = "" And _
Worksheets(sheetname).Cells(currentrow + 2,
GroupCol).Value = "" And _
Worksheets(sheetname).Cells(currentrow + 3,
GroupCol).Value = "" Then
endofreport = True
End If
If endofreport = False And
Worksheets(sheetname).Cells(currentrow, GroupCol).Value = "" Then
MsgBox ("Already done")
endofreport = True

Else
NumRowsGroup = 0
If Worksheets(sheetname).Cells(currentrow,
GroupCol).Value = prevgroupkey Then
NumRowsGroup = NumRowsGroup + 1
If Worksheets(sheetname).Cells(currentrow,
OrderCol).Value = prevorderkey Then
NumRowsOrder = NumRowsOrder + 1
sumorder = sumorder +
Worksheets(sheetname).Cells(currentrow, ValueCol).Value
sumgroup = sumgroup +
Worksheets(sheetname).Cells(currentrow, ValueCol).Value
Else

Worksheets(sheetname).Rows(currentrow).Insert
Worksheets(sheetname).Cells(currentrow,
ValueCol - 1).Value = "Order Total"
Worksheets(sheetname).Cells(currentrow,
ValueCol).Value = sumorder
sumorder = 0
prevorderkey =
Worksheets(sheetname).Cells(currentrow + 1, OrderCol).Value
'put in sum fucntion
End If
Else

Worksheets(sheetname).Rows(currentrow).Insert
Worksheets(sheetname).Cells(currentrow,
ValueCol - 1).Value = "Order Total"
Worksheets(sheetname).Cells(currentrow,
ValueCol).Value = sumorder
sumorder = 0
prevorderkey =
Worksheets(sheetname).Cells(currentrow + 1, OrderCol).Value
Worksheets(sheetname).Rows(currentrow +
1).Insert
Worksheets(sheetname).Cells(currentrow + 1,
ValueCol - 1).Value = "Group Total"
Worksheets(sheetname).Cells(currentrow + 1,
ValueCol).Value = sumgroup
sumgroup = 0
prevgroupkey =
Worksheets(sheetname).Cells(currentrow + 2, GroupCol).Value
'put in sum fucntion
currentrow = currentrow + 1
End If
currentrow = currentrow + 1
End If

Loop Until endofreport
Wend

These are the assumptions/actions:

If there are 4 consecutive empty rows it is assumed to be the end of
the report
If an empty row is found it is assumed that the procedure has already
been run

Hans

Hi

Take a look at the built in Subtotal function in Excel, found under
DataSubtotals.
Also, look at Group and Outline under the same menu and see if these
will help.

--
Regards

Roger Govier


"trav" wrote in
message ...

hans,
First off, thanks for the quick reply.

okay a little background, I know php and mysql, so most of the stuff i
do is moving client to server to client.

I now just started working for a company that uses access and great
plains. I have yet to meet with the Database Admin, so i do not know
the table structure or query structure yet.

Once I get that down, i will start writing the queries myself.

untill then i am forced to use the access program that everyone else
has. in there you can pick an open order report and then it will send
it to excel, (i don't know how, like i said i don't work with
microsoft
products much). the excel spreadsheet then has about 15 columns with
various data in them for each item

in order to make the report more readable, we sort based on the req
ship date and then by order number.

we also delete some of the columns that we don't needs (this is why i
want to set up my own queries, because you can see how inefficient
this
is).

anyway, so I set up a macro that pretty much does that, and adds a
title and date, so we can forward the report to others.

the problem is that there is no break from one group or orders to the
next, so it is hard to see a summary. I have been going through it by
hand and inserting two blank rows in between each group of orders so
it
is easier to read. I also add a sum function to get the total sale
from
the group of each unique order number.

after doing about 200 records i was fed up and thought there had to be
a way to make a macro do this, but i don't know how to set it up so it
looks for unique value groups and preform a task at the end of the
group.

i hope that clearifies it somewhat.

like i said, if they had php running on the server i could pull up a
webpage that would look nifty, but unfortunately they do not, and i
find VB very confusing, but then again i never really use it.

Thanks for the help!!!
travis


--
trav
------------------------------------------------------------------------
trav's Profile:
http://www.excelforum.com/member.php...o&userid=31420
View this thread:
http://www.excelforum.com/showthread...hreadid=511138

THANK YOU SO MUCH !!!!!!

it works GREAT.

you have no idea how much easier you made my life.

THANK YOU

Travis


--
trav
------------------------------------------------------------------------
trav's Profile: http://www.excelforum.com/member.php...o&userid=31420
View this thread: http://www.excelforum.com/showthread...hreadid=511138

okay, so it works great, i am making a few changes to it so that it
makes it a little easier to read. Mainly i just want to bold and add a
line to the top to the cell that the order total is in.

I know nothing about VB, but i guess it would be something like this.

Code:
--------------------

Worksheets(sheetname).Rows(currentrow).Insert
Worksheets(sheetname).Cells(currentrow, ValueCol - 1).Value = "Order Total"
Worksheets(sheetname).Cells(currentrow, ValueCol).Value = sumorder
Worksheets(sheetname).Cells(currentrow, ValueCol - 1).Font.Bold = True
Worksheets(sheetname).Cells(currentrow, ValueCol).Font.Bold = True

--------------------


When i tried that, i recieve no errors but the cells aren't bolded.

and i have no idea how to format a line to the top of the cell.

any suggestions.

also, do you know of any place i can go to find a quick reference guide
to VB syntax

Thanks
Trav


--
trav
------------------------------------------------------------------------
trav's Profile: http://www.excelforum.com/member.php...o&userid=31420
View this thread: http://www.excelforum.com/showthread...hreadid=511138