Sorting Data

Hi,

I was wondering if this is at all possible

I have a vast amount of data that I import every month, the columns remain the same on each import. Its sales data with around 20 columns. I want to extract row data of Product Number, Quantity and Date, Then sort each part number line in a date order by month.
Is this possible??

The only issue is, I am awful at VB :s

The more you learn about excel, the less you will ask "Is this possible?"

Anyway, are you able to upload a sample worksheet?

It sounds like you need a PivotTable. Select the data and click insert --> PivotTable. Setting up the PivotTable is pretty straightforward.

Please find attached example.

Basically, I get sent a copy of date like this each month, which I manually extract and enter in to a Sales Forecast Tracker Sheet, in which I list out each row in Part Number Order and then Date (month) order so I can forecast sales each month

Im looking for a more automated way to extract the Part Number, Measured Date, Quantity, and Unit Price for every Line, and them list them in order of first the Part Number as the master listing, then each set of part numbers to be in date order (mainly the month)

Holy Mackrel, That was quick.

It works a treat, amazing, thank you

I just realised one thing when I ran it though, it errored out I believe as sometimes there is not a measured date (as it hasnt been loaded in to the system fully yet), therefore can you make it so its also keeps the Due Date column, but if there is no measured date in the measured date column, to instead use the due date colume instead (and highlight the row or cell to visually show that row is based on due date and not measured date.

Sorry if that doesnt make sense

One other thing as well, If in the comments column there is the word "RMA", can it just delete that row out of thr data set?

What does that do sorry?

Is it possible to have it do the extra two steps as well?

Ssometimes there is not a measured date (as it hasnt been loaded in to the system fully yet), therefore can you make it so its also keeps the Due Date column, but if there is no measured date in the measured date column, to instead use the due date colume instead (and highlight the row or cell to visually show that row is based on due date and not measured date.

Also, If in the comments column there is the word "RMA", can it just delete that row out of thr data set?

Works perfectly

Thank you

Just wondering, where you have put the columns as the column letter, incase the order gets changes in the future, can you replace the column letter with the header name

for example
wt.Range("Part Nbr" & r).Value = ws.Range("Part Nbr" & r).Value

Not sure I follow you sorry?

two other questions actually,

If ws.Range("X" & r).Value Like "*RMA*" Then
On this line of code, if I want to add other words to delete the rows accordingly, so I just used & "*word*"

Also, is it possible to rather than pull the due date across to the measured date in the output file, add the due date column and leave the due date in that column while sorting?

sorry to keep going on, but is there also a way of creating a new column next to the part number column, with an column called Internal Part Number, then applying a If statement to each row that that for example, IF Part Number = UP819327 then Internal Part Number = BC557, IF Part Number = GT376553 then Internal Part Number = BC375, IF Part Number = KI1504-78T then Internal Part Number = BC963....and so on

Its not in the table, We assigned seperate internal part numbers to each of thos customer part numbers, but it wont be in the list of sales date as per the example. I could create a seperate sheet on the workbook though with all the cross referenced part number if that would help with a lookup element?

So the attached now has a cross reference sheet. So is it possible to lookup the part number, then in the output sheet that the previous code make, add two new columns and put in the "internal Part Number" and "Platform"?

Hi,

Im just having a play with this, trying to add some extra code for some other tasks, hard to learn when you have never done much programming, but slowly figuring it out.

I want to use the section of code that looks for the comment "RMA" and deletes the row, but instead I want to delete all rows that don't contain a specific string this time.

Can you just explain how this section works --> If ws.Range(C & r)

Ok, so the X refers to the column number, but when I change it to a different column number, I cant seem to get it to delete those rows.

For example. Say Column C has the words "Phone" and "Email" in, and I want to delete all the "Email" rows, I would change the code to,

DumpRMA:
If ws.Range(C & r).Value Like "*Email*" Then _
ws.Rows(r).EntireRow.Delete Shift:=xlUp: GoTo DumpRMA

But this doesn't seem to work?

Likewise, If I want to keep all the rows with a certain value in, would I change the Like to IsNot?

Is this the line that defines the columes?

Dim H As String: H = "Part Nbr,Measured Due Date,Quantity Due,Unit Price,Due Date,Comments"

If so, how does it define what headers belong to what column number?

(Sorry for the really basic questions)

Really not understanding that

Am I following correctly?.....

Q = Split(H, ",") - Loads the String defined in H, one by one in to variable Q, so first Q=Part Nbr

Set F = ws.Rows(1).Find(Q(n), , , xlWhole) = Set variable F as the value defined in Q, and assigns it to what column is given in the n values above. So first F = Part Nbr for Column A

S = Replace(F.Address, "$", ""): - Replaces the header for column A in the new sheet with the header defined in variable F

It then repeats for every value of n

?

How do I best write that part mentioned above, regarding wanting to keep all the rows with a certain value in

Ive Tried,

If Not ws.Range(X & r).Value Like "*Email*" Then _
Rows(r).EntireRow.Delete Shift:=xlUp: GoTo DumpRMA

and

If ws.Range(X & r).Value Like "*Email*" Then _
Else Rows(r).EntireRow.Delete Shift:=xlUp: GoTo DumpRMA


but both of those seem to crash excel and I have to close it via task manager

That then seems to interfere with the Next r further down the code (at the bottom of the below)

For r = 1 To ws.Range("A" & Rows.Count).End(xlUp).Row
If ws.Range(A & r) = "" Then GoTo ExitSub
DumpRMA:
If ws.Range(X & r).Value Like "*Email*" Then GoTo GetNextRow
Rows(r).EntireRow.Delete Shift:=xlUp: GoTo DumpRMA
GetNextRow: Next r


wt.Range("A" & r).Value = ws.Range(A & r).Value

Set BC = wt.Range("D" & r)
If ws.Range(B & r).Value <> "" Then _
BC.Value = ws.Range(B & r).Value: _
BC.Interior.ColorIndex = -4142 _
Else: BC.Value = ws.Range(BB & r).Value: _
BC.Interior.ColorIndex = 6

wt.Range("E" & r).Value = ws.Range(C & r).Value
wt.Range("F" & r).Resize(1, 2).Value = ws.Range(D & r).Resize(1, 2).Value
wt.Range("H" & r).Value = ws.Range(X & r).Value
Next r
ExitSub:

oh sorry, got you know

hmmmm, just tried it, but that also made excel hang and crash

just tried to debug it and run line by line, and it seems to be stuck in a constant loop between these two lines

If ws.Range(X & r).Value Like "*POO*" Then GoTo GetNextRow
Rows(r).EntireRow.Delete Shift:=xlUp: GoTo DumpRMA

Originally Posted by xladept

I edited the last post - you do want to process those qualified records, right?

So make the procedure name ContinueProcessing:

Sorry, what do you mean you edited the last post?

perfect, thank you

On this line,

If ws.Range(X & r).Value Like "*Email*" Then GoTo ContinueProcessing

Can you simply add an OR function after the "*Email*" to add another word?

That's working now per the code you posted, but the moment I introduce the second word value is crashes again

If ws.Range(X & r).Value Like "*Email*" OR ws.Range(X & r).Value Like "*Icky-Poo*" Then GoTo ContinueProcessing

Booooooooooom.......works, not sure what the difference was with that new bit of code, but that's solved it a treat. Thank you


You know how the code currently references a sheet called crossref for finding some additional data. Can VB find this if the sheet was in a different workbook instead (but in the same folder)?

Your not wrong, this VB is incredible

Do you just insert this at the top of the code??

Please Login or Register  to view this content.

1 second of glory here, I spotted that you didn't have .xlsx wrapped in "" come on....its a step in the right learning direction for me!

Anyway, its works up until this line --> Set wc = ActiveWorkbook.Sheets("Product List"): ws.Select: Q = Split(H, ",")
at which point is highlights the ws.Select: section with the error of "Method 'Select' of object'_Worksheet' failed"

It did however load up the external workbook ok

Your tooooooo good. Works a treat

I guess I want to close that work book once done, I assume I can use Workbooks("XRefBook.xlsx").Close SaveChanges:=False but how do you work out where to put that cause of all the loops and subs

arrrhhh, sorry, I hadn't copied that bit.

Awesome, thanks mate

On the crossref sheet, some of the crossref values are not being dragged across, like they are not being found. I have checked the part numbers are the same on both workbooks, but it doesn't seem to recognise some of them, any reason you could think of as to why?

Ignore above, I figured out it was a data issue

Hello Again,

I seem to be having a issue with this, and I really can not figure out what I have done to cause this.

The macro keeps throwing an error of "Method 'Range' of object'_Worksheet' failed, and it stops at line If ws.Range(A & r) = "" Then GoTo ExitSub

Any idea?

Got this sort now, I'd messed around to much!!

I am trying to understand this bit of code, but its confused me a lot. What this is doing is take the part number in column A, then cross referencing it to column A in another sheet, and bringing back the values in column B and C of that sheet and pasting them in column B and C
What I can not get my head around is where and how are the columns being selected in this. Essentially, all Im trying to do it to change its slight so that is takes the part number in column A, and this time cross references it to column B in the other sheet, and get the data in column A and C of that sheet and paste them in column B and C again

Please Login or Register  to view this content.

Ok, IM even more lost now You have switch around r=2 and r=4 ?

Tried is out and it doesn't seem to work.

Ok, attached is the layouts.

I have altered the code to fetch the data from the forecast sheet I want. But this time column A of the forecast sheet is crossed to column B of the product list workbook, and I want to return column A and C instead.

sorry, where do I put these element?

Below doesn't seem to work

Please Login or Register  to view this content.

So the code you wrote before, I want the exact same task of is sorting through the values and deleting the ones I done want. That part worked ok after I changed some of it. But the section of code where it goes off to the product list, and cross references the part number, I just wanted to change that section to section from different columns

So originally you wrote this bit of code

Please Login or Register  to view this content.

Which works great, and I have used it. I am using it on a different sheet now as well, exactly the same scenario only I changed some of it slightly.
But, this time, on the below section of code, I wanted it to cross reference column B (instead of A) of the part list, then return column A and C (Instead of Column B and C)

Please Login or Register  to view this content.

What code are you referring to as worked? The one I just posted?

The one I just posted is the original, which after its collated and sorted the data goes off to the Part List sheet/workbook, looks up the part number in column A of the part list, and then returns the data from column B and C of the part list against the match row in A column of the forecast.

This time, the data in the new sheet I am setting up, the part number is actually the one in column B of the Part List, and I want to return the other part number in column A of the part list and the platform in column C. If you run the sheet I sent you earlier, it doesn't do this, Infact is doesn't seem to return any value.

Note: I still want the functionality of it sorting through the data in the forecast sheet, and placing it in the sorted tab in a sorted order.

If you run that spreadsheet I sent you earlier, that part of the VB doesn't fetch the data. I believe its because ori

Im really lost mate, im not great with VB, apologies.

So you posted

Please Login or Register  to view this content.

But don't I need to insert that in to this

Please Login or Register  to view this content.

If so, where?

error on that as r As Long is repeated, but when I delete one the DIM goes black and errors

Thanks, Please find attached

That didn't work either, however, I think I have sorted it with the old code you made for me.

Granted its probably not the most elegant way to do it, but I learnt quite a bit trying to figure it out. I realise that the code doesn't select each column from the product list table, it instead selects a cell then uses a resize function to select the adjacent cell, and I think that's what I originally didn't understand as I was trying to select A and C, so they were not adjacent.

In the end, what I have done is copied specific section of code twice, and change the selection cell, but made it only paste the value in to 1 column rather than two.

so I ended up with this

Please Login or Register  to view this content.

I did try and remove the resize part of this

Please Login or Register  to view this content.

but it kept throwing an error. So I basically changed this section

Please Login or Register  to view this content.

so it only returned in to one column.

As I said, this is probably an ugly way of doing it, so would be keen to know how you would tidy this up

Full code below

Please Login or Register  to view this content.