New worksheet based on multiple worksheets

[abhi_jain80]

Hi Experts,

I have two worksheets - "Transactions" and "PO". I need a new sheet to be created on the click of the button.

This new sheet should be similar to the "Output" sheet. It should have unique Item# and Descriptions along with the more columns added. Columns C:D should be based on "Transactions" sheet and Column H should be based on "PO" sheet. The respective formulae's have been mentioned in the "Output" sheet for your reference.

Hope I am clear with the requirement. The sample data along with the output has been attached. Please support. Thanks is advance...


Regards,
Abhi

[maras_mak]

My attempt.

Please Login or Register  to view this content.

[sintek]

Another Option...

Please Login or Register  to view this content.

If huge data set, I would do calculation and not use Evaluate...

[abhi_jain80]

Many Thanks sintek for your reply.

The code is working fine for the sample data I provided but if I input the raw data, I am getting the "Run Time Error 13 - Type mismatch".

The raw data contains thousands of rows and there could be some missing values. Attaching it for your reference.

Regards,
Abhi

[abhi_jain80]

Many Thanks Maras for your reply.

The code is working fine for the sample data I provided but the excel is not responding if I input the raw data. The raw data contains thousands of rows and there could be some missing values. Attaching it for your reference.

Regards,
Abhi

[sintek]

Attaching it for your reference.

Where.........
Also very important...In future always upload a sample file depicting your actual file setup so no time is wasted on providing incorrect code...
Almost all the time the person is not able to amend the code for their actual file...

[abhi_jain80]

Where.........
Also very important...In future always upload a sample file depicting your actual file setup so no time is wasted on providing incorrect code...
Almost all the time the person is not able to amend the code for their actual file...

Apologies for that...actually I didn't realize that my sample data is not depicting the actual data. I am getting the error in the If loop you have provided.

[sintek]

I am getting the error in the If loop you have provided.

I am sure you say you are...I cannot test as I do not have the file you are referencing...

as per your Post 4

Attaching it for your reference.

Where...Nothing was attached...

[abhi_jain80]

I am sure you say you are...I cannot test as I do not have the file you are referencing...

as per your Post 4

Where...Nothing was attached...

It's a big file. Attaching it is the binary format. Hope should be fine. Thanks

[abhi_jain80]

Hi sintek, I need the unique item# from the "Transactions" data and pull out its info from the "PO" data. Just thought to mention if I was not clear before.
Many Thanks, Abhi

[sintek]

Is because Col A is numbers and texts...
This will work on small data set but as I mentioned above...For huge datasets it is better to do calculation directly instead of evaluate...
No time right now but will have a look at it a bit later...

Please Login or Register  to view this content.

[abhi_jain80]

Is because Col A is numbers and texts...
This will work on small data set but as I mentioned above...For huge datasets it is better to do calculation directly instead of evaluate...
No time right now but will have a look at it a bit later...

Hi sintek, I had a thought on how to do the calculations directly as you have suggested for huge datasets but my bad, not able to get it. Can you have a look when you have time please?
Thanks in advance...Regards, Abhi

[sintek]

Can't offhand see any other way of doing some of these calculations as array formulas are required...Perhaps someone else can hop onboard and offer their input...

[abhi_jain80]

My attempt.

Please Login or Register  to view this content.

Hi Maras,

Today I ran the code again on huge data and amazingly it worked. I left the system idle and it took almost 30 mins to run the code. Is there any way we can optimize the code to run faster? Attaching the data for your reference.

Thanks,
Abhi

[sintek]

Best I can do is 3 min...Data is sorted Col A lowest to highest...

[sintek]

This version takes 101.83 seconds...

[abhi_jain80]

This version takes 101.83 seconds...

This is perfectly working fine. Though for me it is taking around 7 minutes, might be to system configuration. But still manageable.

One query...what if I want to find the minimum quantity from the "PO" sheet instead of summation, then the code line:

Application.MinIf(Sheets("PO").Range("C2:C" & lr), Data(i, 1), Sheets("PO").Range("B2:B" & lr))), ";") is not working.

Is there any other property to find the minimum value?

Thanks, Abhi

[sintek]

Perhaps...

Please Login or Register  to view this content.

[abhi_jain80]

Perhaps...

Please Login or Register  to view this content.

With this, I am getting the run-time error - Type mismatch. It seems like I am doing some silly mistake. See attached

[romperstomper]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

[abhi_jain80]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

Thanks Rory for letting me aware. I missed the forum rules, my sincere apologies. Will be taken care in future.

Here is the cross-post link but there is no solution posted, and also I have closed the thread over there.

https://chandoo.org/forum/threads/cr...ksheets.46305/

Thanks again. ..Abhi

[sintek]

With this, I am getting the run-time error - Type mismatch

See attached....

[dangelor]

This code took a little under 3 seconds on my laptop...

Please Login or Register  to view this content.

[abhi_jain80]

See attached....

Woww. This has solved my query. You're superb. Thanks Man.

[sintek]

@dangelor...Nicely done...

[dangelor]

Just a different approach... Thanks for the compliment and the rep!

[abhi_jain80]

See attached....

Hi Sintek,

The code is working fine for most of the dataset. But for some items it is pulling the false descriptions and for few items, the descriptions got split into columns.
See attached....The "Transactions" sheet only contains the data in which I am getting the wrong output.

Just wondering why it is only for few items and only with descriptions, something strange for me.

Thanks, Abhi

[sintek]

Why not go with dangelor solution...

[abhi_jain80]

Why not go with dangelor solution...

Let me see that way if it works...thanks

[abhi_jain80]

This code took a little under 3 seconds on my laptop...

Please Login or Register  to view this content.

Hi dangelor,

As sintek recommended, I am using your solution. The code is absolutely working fine on my raw dataset. Can you please suggest on how to add two more columns in the "Output" sheet for minimum and average of the quantities from the "PO" sheet? Apologies if I was not clear at the first instant.

Many Thanks, Abhi

[abhi_jain80]

Hi dangelor,

As sintek recommended, I am using your solution. The code is absolutely working fine on my raw dataset. Can you please suggest on how to add two more columns in the "Output" sheet for minimum and average of the quantities from the "PO" sheet? Apologies if I was not clear at the first instant.

Many Thanks, Abhi

Hi Dangelor,

I have tried to add one more column in the output for average quantity ordered, but my bad...not getting the accurate results. I know I am doing some silly mistake, can you please have a look when you have time please?

'Average Quantity Ordered
v = Sheet4.Cells(1).CurrentRegion
With CreateObject("Scripting.Dictionary")
For i = 2 To UBound(v)
If .Item(v(i, 3)) Then
.Item(v(i, 3)) = WorksheetFunction.Average(.Item(v(i, 3)), v(i, 2))
Else
.Item(v(i, 3)) = v(i, 2)
End If
Next i
For i = 2 To UBound(sku1)
sku1(i, 1) = .Item(sku1(i, 1))
Next i
Sheet1.Columns(9).NumberFormat = "0.00"
Sheet1.Cells(1, 9).Resize(i - 1) = sku1
Sheet1.Cells(1, 9) = "Avg Qty"
End With

Thanks, Abhi

[dangelor]

When I can..

[abhi_jain80]

When I can..

Thanks dangelor for your support. Also just I observed that the average cost for some items are not coming accurate, for ex: for Item# 22300405, the average cost coming in output as 124.2004, while actually it is 120.29. Please look into this also. Thank you.

Regards, Abhi

[dangelor]

I noticed that last night. Still working it, but life interrupts at times...

[abhi_jain80]

I noticed that last night. Still working it, but life interrupts at times...

Yes, I can understand. Believe me, I spent around 14 hours on this but could not be able to figure out the solution

[dangelor]

Sorry for the delay... finally got the average right, and it's a bit slower.

Please Login or Register  to view this content.

[abhi_jain80]

Sorry for the delay... finally got the average right, and it's a bit slower.

Please Login or Register  to view this content.

Thanks dangelor for working on this, but apologies as still I am seeing the inaccurate average for some of the items.
for ex: the average cost in the output for item# 22300403 is 118.54 but actually it is 119.66. Similarly for 22300404, in output it's coming as 131.98 and actually it is 133.10

Thanks, Abhi

[dangelor]

When I run the numbers (Total cost / Total qty) manually for those two items, I get the averages listed by the code.

[abhi_jain80]

When I run the numbers (Total cost / Total qty) manually for those two items, I get the averages listed by the code.

My bad...I figured out my mistake. Thank you so much.

Something weird I have observed today. Most of the dates outputted in the "First Issue Date" are in right date format but few came in text format...like over here for item# 22300406 and 22300420, the dates are in text format while in the base data, all are in the correct date format. Might you are aware of the quick solution for this.

[dangelor]

Try it this way...

Please Login or Register  to view this content.

[abhi_jain80]

Try it this way...

Please Login or Register  to view this content.

Still the same issue. I realize that the dates are not in the date format where there is only one transaction. see attached...thanks

[dangelor]

Try changing the date formatting...

Please Login or Register  to view this content.

[abhi_jain80]

Try changing the date formatting...

Please Login or Register  to view this content.

I changed the date format to "d/m/yyyy" but sorry to say, still the same problem

[dangelor]

I don't know what could be causing it. It doesn't happen on my systems.

[Marc L]

Hi,

same issue 'cause of the classic trap of a range without any property …

Solved on my side with vT = Range("Transactions!A1").CurrentRegion.Value2 …

[abhi_jain80]

Hi,

same issue 'cause of the classic trap of a range without any property …

Solved on my side with vT = Range("Transactions!A1").CurrentRegion.Value2 …

Great Marc, this has solved my issue. Thank you so much.