Check unique parts, copy to another sheet, find cost for each part by date

Stigmata101

New Member
Joined
Feb 27, 2014
Messages
25
Hey guys

I have code to complete the first step, identifying unique parts and moving them to the new sheet, thanks to a member of the forum from another post of mine. I am leaving it out in the hopes that I will get a single solution.

Basically what I am trying to do is determine the trend of the cost of parts over a few days.

The first step is to collect all the unique part numbers and copy them to the Trend sheet. Then to find all the values for the previous 5 date entries, these dates are not sequential.

Once all the costs are in the table, I need to evaluate the trend, to determine if there is no change, flat trend, positive trend, or negative trend, increasing or decreasing cost values.

As I said, I have code to do the unique part move and I am using formulas to vlookup the costs and to do the trend analysis. The problem is that there is a large amount of data and the number of formulas kills the workbook.

Sample Workbook:

Appreciate the assistance.
Stig
 

Stigmata101

New Member
Joined
Feb 27, 2014
Messages
25
No Worries Akuini, you helping me mate, super appreciated.

FYI, made a change to get the text and comma format:
This changed
VBA Code:
'adjust sheet name
Sheets("Result").Activate
Range("A1").CurrentRegion.Offset(1).ClearContents
Range("A2").Resize(j, 6) = vb

Now this:
Code:
'adjust sheet name
Sheets("Result").Activate
Range("A:A").NumberFormat = "@"
Range("B:F").NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Range("A1").CurrentRegion.Offset(1).ClearContents
Range("A2").Resize(j, 6) = vb

I tried to change the trend analysis but keep getting a type mismatch error.
What is tried:
Code:
If va(i, 3) > 0 and va(i, 3) >= va(i, 2) And va(i, 2) > va(i, 1) Then
        vb(i, 1) = "Positive"
    ElseIf va(i, 3) < 0 and va(i, 3) <= va(i, 2) And va(i, 2) < va(i, 1) Then
        vb(i, 1) = "Negative"
    ElseIf va(i, 3) = va(i, 2) And va(i, 2) = va(i, 1) Then
        vb(i, 1) = "Flat"
    End If

I think it has something to do with the va(i, 3) > 0, just not sure how to correct this.

The idea is that I only want to test for a positive trend if the latest cost is greater than 0, also, if the latest date and previous date cost are the same it can be deemed positive. Similar to a negative trend, just when less than 0.

I am going to keep trying and will update if I get it right.
Stig
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,653
Office Version
  1. 365
Platform
  1. Windows
I tried to change the trend analysis but keep getting a type mismatch error.
I don't think it's because you changed the if statement.
In which line the error occured?
I tried your new if statement on your sample data, it worked fine.
 

Stigmata101

New Member
Joined
Feb 27, 2014
Messages
25
Hey Akuini

It seems to be something with the formatting that I did in the results tab. When I run it on the sample excel you shared, it works.

I am using the below to format the cells in the results tab:
VBA Code:
Range("A:A").NumberFormat = "@"
Range("B:F").NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"

When I do this I get the type mismatch on the very first If statement
Code:
If va(i, 3) > 0 And va(i, 3) >= va(i, 2) And va(i, 2) > va(i, 1) Then

Stig
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,653
Office Version
  1. 365
Platform
  1. Windows
I tried your code on the example, it works. So maybe something wrong with your actual data?
If you use General Format in B:F, does it work?
 

Stigmata101

New Member
Joined
Feb 27, 2014
Messages
25
So, it seems to not like it if a #N/A is returned in column B:F.

I did try general formatting and that made no difference. I found the missing data and am correcting it now.

Tested it now on my sample data and everything seems to be working correctly.

Going to give it a go with the full data set tomorrow and see what happens.

Off-topic, do you have a YouTube channel where you teach this method? It works really well and I would love to get a better understanding of it.

Stig
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,653
Office Version
  1. 365
Platform
  1. Windows
No, I don't have a Youtube channel. :)
Basically, to speed up the process you need to minimize traffic between VBA and the worksheet. And usually I use array and Dictionary object to do that.
Here's a good source:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,396
Messages
5,769,841
Members
425,574
Latest member
grimeslisa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top