complex matrix data counting and extraction problem

There is probably more mistakes and omissions that i am not aware of. i just hope i packed enough concept for you to run with it. please let me know if you have questions. I just been looking at this thing for so long i am getting tired.

I just realized i keep replying to myself. hopefully no more - your turn.

heeeeelp.

pleeeeese

It will be helpful if you explain how you arrive at your result sheet. If i look at it, it does not make sense to me.

Here is what the result should look like AFTER and BEFORE running the Macro. The VB doesnt have to be short or super neat- just intuitive and simple enough for a novice to go in and revise it if the columns, rows and data changes.

why do you need vba-formulas would do this quite easily?

in this version of your file I have added formulas in columns to the right of your original result table for comparison. it's the same formula in every cell so you can copy it across and down as required

Joseph,
Your formula works great for this setup. i see you got it to work nicely-dragging horizonally and vertically. i like your formula . BUT the reason i still need a VBA is because it would automatically pull in the Part Number and Description from the RawData sheet and populate the perspective result to the other other sheet- I just need to pre-populate the headers as shown in the attached images and the code would do the data gathering and calcualtiions. What I am trying to do is minimize human errors by minimizing copy/paste of data. And it is not a stright forward copy and paste; you have to manually line up and match horizonal data to vertical data and such (Vlookup stuff) - increased chance of errors (and i make lots of them.) and the file is going to be this large with lots of rows and columns AND others working on the file as well.

Hi Red fuji

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

Hello jaslake,
It certainly does the job well ! Prep_Data Macro calls on Populate_Results Macro - geniously clever setup. just need to understand it.

i just need to study the Macros and tweek to accomodate my actual file which has info on different columns and additional columns.
You are a life saver.

You've crossposted this at the Code Cage Forum. I replied there that you shouldn't need VBA, but to use Formulas instead

Hello Jaslake,
I want to personally thank you for the tremendous effort you put in creating this complex macro- it certainly does the job well. I don’t know how else to thank you for the time and effort you taken from your personal time in tackling this sophisticated problem. I was getting desperate for a solution and you came in at last minute with an eloquent VBA. I don’t know much about you but I wish you great success and fortune in whatever venture you face-you certainly deserve great kudos not just for understanding the problem but for coming up with a working solution and most importantly for offering it to a complete stranger in times of need. if the thread allowed for more points you certainly deserve a ten.

Hi Red fuji

Is this new workbook a replica of your actual file in structure? Does the actual file have 17 columns?
Are there ONLY 14 Containers? Are the Container Numbers and Container Descriptions ALWAYS the same?
Will Container Description ALWAYS occupy Cells A3 through A16?
Will Container Number ALWAYS occupy Cells D2 through Q2?

Hi Red fugi

Moving targets are difficult to hit. This Prep_Data code will work on your most recent attachment (the Populate_Results code remains the same based on this file structure). If you're really interested in modifying the code for other worksheet structures you can analyze the changes I made from the original code to this code. I believe I changed only two lines of code (I left the old lines so you can see the changes).

If you have 3 or 4 or 5 structures you're dealing with perhaps we could make a Universal Prep_Date macro but if the structure is not going to be even that consistent (3 or 4 or 5) then it would be very difficult for one not familiar with your structures.

Anyhow here's the revised code...let me know what I can do to help.

Please Login or Register  to view this content.

Jaslake-thank you
easy to revise:
ActiveWorkbook.Names.Add Name:="Description", RefersTo:= _
"=RawDataSheet!$B$3:$B$16"

If you dont mind , i inserted four additional columns with random data and blank cell to simulate a Universal Structure-I think it will do it.
Thank you !

I am sorry i was in a hurry- i guess data in row 17 on downdown for the additional columns dont matter anyway.- empty cells where go where it matters: row 3 to 16.

Hi Red fugi

I'm trying to develop an approach to and relationships of data regarding your issue. Couple of questions regarding your most current attachment Copy of Matrix Data v1.2_jaslake_table moved v1_basic structure.xlsm.


In RawDataSheet:

• In Row 2 the Container Numbers all have 90 Degree Orientation...is this ALWAYS true?
• In Row 2 the Container Numbers COUNT happens to be 14 and the COUNT in Column D (PartNumber) from D3 to D16 also happens to be 14...will these counts ALWAYS be the same? Such that if there are 14 Container Numbers in Row 2 there will be 14 Part Numbers in the PartNumber Column (whatever Column that happens to be)?

Hi jaslake
I had to step away from computer-
In Raw Datasheet;
* In Row 2 the Container Numbers all have 90 Degree Orientation...is this ALWAYS true?
Yes. It just allows the column width to be narrow to keep the data from going too far outside the screen because I could have up to 150 Container Numbers.

* In Row 2 the Container Numbers COUNT happens to be 14 and the COUNT in Column D (Part Number) from D3 to D16 also happens to be 14...will these counts ALWAYS be the same?
YES. The reason Container Numbers are repeated vertically is because there are more properties such as Location/Manufacturer/Uses/Person ,etc. that I cannot possibly list horizontally. The horizontal listing is for talling up the quantities and the vertical listing for the additional properties.

Hi Red fugi

There was a question I failed to ask

•In Row 2 the Container Numbers all have 90 Degree Orientation

Are these the ONLY cells in Row 2 that have 90 Degree Orientation?

Hi jaslake,

"Are these the ONLY cells in Row 2 that have 90 Degree Orientation?"
Not really, There are other headers oriented at 90 - for space saving not critical. I think I see that you are trying to clarify my vague needs-thanks. I didn’t want to go too far in asking for the headers as well. The problem is complex enough. The second sheet is really a report that I am passing on to my superiors. The report should have at minimum the basic columns that have already been included in the VBA. The other columns are added upon request or as needed-thus I want to keep their addition open- i just need to id them on the VBA to activate/deactivate.

Status count for each container is the core of the report (VERY GOOD, GOOD, BAD, QUESTIONABLE, NOTRATED). Everything else is 'properties' of the CONTAINER NUMBER. The 'properties' columns are carried over from sheet one to sheet two per need.

Hi Red fugi

Well this complicates matters on the approach I've taken

"Are these the ONLY cells in Row 2 that have 90 Degree Orientation?"
Not really, There are other headers oriented at 90

So, are the Container Numbers in Row 2 ALWAYS contiguous (grouped TOGETHER...NEXT to each other)?

HI jaslake,
yes, the CONTAINER NUMBERS set in row two will always stay together...listed right next to each other..consecutive columns.
I can change the orientation to 0 degree for all the optional columns if you like but keep the CONTAINER NUMBER columns set at 90. It is just that i can have up to 250 columns for some projects and I am trying to condense column width if i can but it is OK-i can keep orientation consistent at 0 for the optionals.

Hi Red fugi

I'm looking at another approach where the User highlights the Container Numbers in Row 2...and it'll work...IF you can live with User Input.

I have working code assuming Container Numbers are the ONLY cells in Row 2 with 90 degree orientation but it's not practical you do this with 250 Columns.

I can change the orientation to 0 degree for all the optional columns if you like but keep the CONTAINER NUMBER columns set at 90

I'll get back to you...if you CAN'T live with User Input...YOU get back to me.

Hi Red fugi

The code in the attached asks the User to Highlight the Container Numbers in Row 2...having done so the code creates the Results detail. I believe the code works regardless of the number of columns or the location of the columns in RawDataSheet. It doesn't care where Description or PartNumber or PartNumberStatus columns are located...it finds them. Test the code on the attached...if it works as expected, test the code on a COPY of any of your live files. Let me know the results.

Regarding this

Can the user highlight certain property columns too in a secondary operation?

I don't know what you have in mind...we'll need to look at it.

Hi Red fugi

I do need to output certain properties for Container Numbers. can the VB let me select which properties to include in the report?

Probably...show me what you have and what you'd like it to be.

Hi Red fugi

Well the key to all the code is PartNumber so I'd suggest you need to prepopulate Results Sheet Columns A (Description) and B (PartNumber) along with VERY GOOD, GOOD, BAD, QUESTIONABLE, NOTRATED.

The question becomes, besides the prepopulated Columns, do you wish a LIST of the NOT chosen Columns or do you want a list of those Columns you MIGHT choose from. Makes a bit of difference. If the latter...what's the list?

Hi Red fugi

You could possibly do this

I was thinking i could pick and choose the columns by highlighting as you did with the Container numbers

My question is DO YOU WISH to do this. I'd think there are SOME Columns you're not interested in displaying...so, is there a LIST of Columns you may wish to display...or do really want them all as an option?

Hi Red fugi

Alrighty then...we'll

pick-and-chose them on the fly

Won't be tonight though...have duty in the morning. I'll get back to you when I figure it out.

Hi Red fugi

The code in the attached appears to do this

i will like to display ALL the columns... Carry them over to the result sheet

Let me know of issues.

Hi Red fugi

Why

trying to keep the pre-populated text limited to VERYGOOD, GOOD, BAD, QUESTIONABLE, NOTRATED-Those five columns only

You don't need to do this...the Code does it

when I delete entire columns H to M and run the macro, it replaces G1 with the text "COLUMN_1"

If you insist on doing so you'll need to add these two lines of code to Prep_Data macro

Please Login or Register  to view this content.

Hi Red fugi

The only issue I have with your Thread is you don't have a clear idea of what you want/need. As I've said. moving targets are difficult to hit.

What will go into the first two columns in your view of things (Columns A & B of Results)? Where will PartNumber and Description be, where ever they happen to land?

Please think about and define your needs...then we can write code to accommodate those needs. Get back to me.

Hi jaslake,
As I got to understand how the code works and how each decision effects the code's complexity, i decided It is not important where each column lands on the Result sheet. As long as the data mates up horizontally I am fine. One of my top priorities is being able to revise the code as needed. And since I am only a novice at that i have to pick and chose what features to sacrafice for the sake of keeping the code as simple/intuitive as possible. If the code is too complex i wont be able to adjusted to changing data. I am trying to keep it one size fits all kind of thing. One of the highlights of this code is that you have it set it up so that i highlight the CONTAINER NUMBERS and the Code does the rest; i dont even have to open VBA to make changes if the number of containers increase or decrease or if the columns shift. I LOVE THAT kind of flexibility. and you already have a lots of those user-friendly features built in-If i shift a column the code still works so it is mainly with shifting columns that i worry about. I could have a project with the DESCRIPTION field in column F and in another project it will bein column ZZ. I have to adjust for those changies. But if the code allows for that flexibiiltiy then I love it. Another example why I need the flexibility is that in one project the DESCRIPTION cell might change to say PART DESCRIPITION so now i have to revise the code to match what's in Excel.
I cant be too demanding to the point that it makes the code to complex. It might be a simple thing to revise the VBA but not for me-at least not today. So you see where I am coming from? Thanks again !

I do admit that i didnt have clear requirements from begining.

Hi Red fugi

What other kind of anomalies are there like this

Another example why I need the flexibility is that in one project the DESCRIPTION cell might change to say PART DESCRIPTION so now i have to revise the code to match what's in Excel.

Hi Red fugi

Let me look at it.

Hi Red fugi

I believe the code in the attached resolves this

The columns in the RawdataSheet are not in any specific order; they could move and shift all over the worksheet. Even the number of populated columns could vary from worksheet to worksheet

and this

The core columns of this project are: PARTNUMBER, VERYGOOD, GOOD, BAD, QUESTIONABLE, NOTRATED . These can be listed in that order starting at Column A of ResultSheet.

and this

Columns other than those are 'property' columns ... and can be placed in any order directly after those.

and this

the Macro to generate everything in the Result sheet (instead of pre-populating)

and this

Another example why I need the flexibility is that in one project the DESCRIPTION cell might change to say PART DESCRIPTION so now i have to revise the code to match what's in Excel.

with this code

Please Login or Register  to view this content.

Notice the addition of the asterisk (*) in the above code.

and this

If you can combine the two VBA modules into one that would be great

All the code is in one Module...they're still separate Procedures because that's my writing style...each procedure has it's own purpose and accordingly are compartmentalized.

Let me know of issues.