complex counting file

[Red fuji]

Ok- this is a matrix problem that Jaslake did a fabulous job solving. Now I need a different kind of report pulled plus I added two new part numbers that collect the other 14 CONTAINER NUMBERS. a STATUS and QUANTITY has also been added to those 14 container numbers- thats the only difference.

I have a File with a bunch of data that need to be counted and matched up. The file has two sheets to be worked. The RawDataSheet has the raw data and the PartCountSheet has what the result should look like. If you filter for each CONTAINER NUMBER in row 2 you will come up with the result you see in the PartCountSheet ( I did not do all 16 container, I only did 4 of them just to show you how the result would look like). There are 16 CONTAINER NUMBERS total. The last two containers in red actually collect the other 14 containers. And those 14 containers collect the part numbers in rows 19 to 118. So you have a nested kind of setup. Thanks.

[jindon]

Try

Please Login or Register  to view this content.

[Red fuji]

Looks great ! I am going to copy and paste your VBA into multiple files. unfortunately each file is set up a little different regarding the column placement. they are shifted around. Example , the INDEX column could be in column A in one file and column Z in another file. Is there any way the code could automatically capture that irregularity. Maybe by reading the column heading? This irregularity is only found in the RawDataSheet. The PartsCountSheet column setup is the same for all the files. Thanks!

[jindon]

I think it is possible only if col.E:T of RawDataSheet are always fixed.

[Red fuji]

Hi jindon,
yes , I can keep columns E:T fixed. but i dont know how to revise the code. Thanks

[jindon]

Or
Is it possible to identify the columns of "CONTAINER NUMBER" anyhow?

If it is possible, it can be done in any order.

[jaslake]

Hi Red fuji

In sheet PartCountSheet will Column Headings A ---> G be static (ALWAYS in the same order)?

[Red fuji]

John !
Yes, A to G are static. But the rest of the columns(properties) in RawDataSheet will also carry over to the PartCountSheet in no particular order though.
Thanks.

[jaslake]

Hi Red fuji

See if the code in the attached does as you require. Let me know of issues.

[Red fuji]

Hi John,
Very well done! It does excatly what i need ! I am going to study the code a bit- it is in a class of its own.
Thanks !

[Red fuji]

Hi John,
I deleted row 1 (CONTAINER NUMBER) from RawDataSheet (rows shift up one) and now I am having trouble getting the code to work again.
I went throught the code replacing the 2 with 1 and the 3 with 2 but I must be doing something wrogn - I keep getting a horizontal mismatch of data.
I am lost now as to what lines of code I should change to get the code working to the deleted row.

Thanks.

[jaslake]

Hi Red fuji

Check the attached...see if I got them all...let me know of issues.

[Red fuji]

Hi John,
it appears to run fine but the data is mismatched across.
also Also, is it possible to make it function like the other Macro where if you added a new CONTAINER numbers in random column and row it would still pick it up. That would be great making it fool proof.

Thanks

[jaslake]

Hi Red fuji

I'll look at this...must have missed something

the data is mismatched across

I have no idea what this means...post a sample file that exhibits this

if you added a new CONTAINER numbers in random column and row it would still pick it up

Please explain what's different in the new attachment so I don't need to look for it (unless it's obvious).

[Red fuji]

Hi John,
I added a container number in column U & Row 16. This would go against the normal order of the previous listing. Can you make the code work with that anamoly ?

Thanks !

I also did trial and error changing -2 to -3 and seems to work for some cases but not the anamoly above.

Please Login or Register  to view this content.

[jaslake]

Hi Red fuji


For the moment let's forget about the anomaly above. Does the code (with this change) do as you require?

.Range("B" & wsTgtLR).Resize(lCnt, 1).Value = wsSrc.Cells(i - 3, descCol).Value

Let's deal with one issue at a time...get it resolved and move to the next issue.

Run your revised code on a workbook with this row deleted

row 1 (CONTAINER NUMBER) from RawDataSheet

but also without

added a new CONTAINER numbers in random column and row

Does your revised code give you what you require?

[Red fuji]

Hi John,
I am always mistakenly assuming that the code will do what I want even if I don’t communicate the need.
I owe you an apology for not putting forth all the requirements from day one- I know it is causing you unnecessary rework and headache.

[Red fuji]

Hi John,
Yes it works with that code revised.
Yes it works with row one deleted.
Yes it works without the addition of the anamoly Container Number.

[jaslake]

Hi Red fuji

Alrighty then...I can now work on the anomaly.

[jaslake]

Hi Red fuji

I run the code on your attachment from Post #15 and it appears to run as desired. The only thing I see is that CCC is at the end of the list and not before WW_A and WW_B. Is that what you're looking at or do you see something else?

[Red fuji]

Hi John,

The only thing I see is that CCC is at the end of the list and not before WW_A and WW_B. Is that what you're looking at or do you see something else?

yes, CCC listed at the end is OK but look at column B for its Description and you will see that it pulls in WW_B_DESC which is not the description for CCC. It is pulling in the wrong description for these part numbers: WW_A, WW_B, and CCC. can it do a Vlookup kind of code to pull in the right description rather than follow a chronological order?

Thanks!

[jaslake]

Hi Red fuji

I see said the blind man...I'll need to study it.

[jaslake]

Hi Red fuji

I'm on it.
Is PartCountSheet populating correctly EXCEPT for Column B? Let me know ASAP...I'm headin' down a path and wish to make sure it's the correct path.
I did find an issue with Results sheet (the Header Row) but fixed it...the issue arose from deleting the 1st row in Raw Data.

[Red fuji]

Hi John,
Yes column B only.

[jaslake]

Alright...I know what needs to be done...have to figure out how to do it...get back to you.

[jaslake]

Hi Red fugi

See if the attached puts things where they need to be...let me know of issues.

[Red fuji]

Hi John,
Thank you
You are a VBA prodigy! works first-class.
I just need to comprehend your code in order to do minor customization per my specific project. First off- i need to learn how you got it to invoke the Sub Get_User_Input when i dont even see it with the rest of the Module.

[jaslake]

Hi Red fugi

There is only ONE copy of this

invoke the Sub Get_User_Input

It depends on which Command Button is pushed as to what it does...look at it, you'll figure it out.

[Red fuji]

Hi John,
Yes - figured that one out ! took me some screen staring-it was right there staring at me I did manage to combine all four subs into one module. It might not be a good practice in the VBA world but my aim is to make the code as 'dummy' friendly as possible; eliminating duplicated variables and lines whenever possible-I am still doing trial errors on that. your code is defenitely an education for me. I will probably need your assistance, if i may, when i get stuck. Another future project aim is to make this VBA Module available to multiple Excel files. I will keep you informed on that. I am taking it one step at a time -not to overwhelm myself. Thanks !

[jaslake]

Hi Red fugi

Glad you got it sorted out...let me know how I can help.

[Red fuji]

Hi John,
I relocated the four sub routines into one Module. It was an effort but it works. It is attached. ( i also changed some of the wording like Status to Color, etc. )now I need to eliminate/reduce the repitive statements across the four subs.
How can I eliminate the repetitions and move a single occurane of them to the top of the module and make it available for all four subs to use.
I am doing this so that if I have to revise a statement in the future, I don’t have to revise it upto four times instead of revising it only one time- I prefer to move all these revisable statement to the top of the module if possible. Is that doable? (even though it might be against best practice.)? I am also tyring to reduce the number of lines if i can.

Some example repeated statements- there maybe more and I want to reduce them all:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

My next task wil be to make this nice fool-proof Module available for multiple Excel files. Not sure how to accomplish that yet.

[jaslake]

Hi Red fugi

The code in the attached is revised for some of what you described.

[Red fuji]

Hi Jaslake,
I like it very much; thank you!

[jaslake]

Hi Red fugi

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save