macro to look for color total

[meezadahsra]

Hello experts.

so here is my problem.

style no. colorcode someinfo someinfo someinfo someinfo
abc123 03 xxx xxxx xxx xx
xxx xxx xxx xxx
COLOR TOTAL | 6 xxx xxx
06 xxx xxx xxx xxx
xxx xxx xxx xxx
COLOR TOTAL | 3
pqr132 14 xxx xxxx xxx xx
xxx xxx xxx xxx
COLOR TOTAL | 9
47 xxx xxx xxx xxx
xxx xxx xxx xxx
COLOR TOTAL | 2 xxx xxx



i want to write a macro which combines the style no. with the color code and has the color total value. e.g.

abc 12303 6
abc12306 3
pqr13214 9
pqr13247 2

the output has tocome in the next sheet.

i can manually do this, but the problem is i have 16000 rows to do it and manually doing it can make me go crazy.
the color total is not in the starting column but some where in the 8th or 9th coloum.
so preferably the macro has to look for the value next the to color total for the specific style no.+color code.


someone please help me. thanks

[Richard Buttrey]

Hi,

For these sorts of requests it's always easier, and saves misunderstanding, if we can see your request in the context of its workbook.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitise the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[meezadahsra]

attaching the sample xls

[Richard Buttrey]

Hi,

Does your data really change its layout on Page 2 or is this a typo?
For instance the Units column on Page 1 is column I but on Page 2 it's column H.

I had originally developed a solution without macros when I stumbled across this difference which complicates things immensely.
If your data is consistent then it will be relatively straight forward but if not it won't.

Please comment before I move this forward, and if it's not consistent how does it vary from page to page over several pages?

[meezadahsra]

yes originally the units column changes, but i sat down and set it in such a way that all the units stay in the same column.
i am attaching the original file. hope u get a better idea.

please unhide all the columns to have a better view.

[meezadahsra]

also note that the style numbers from row no. 15667 to 15771 are in row B, which are supposed to be in row A.

[meezadahsra]

refined xlxs sheet

[Richard Buttrey]

Hi,

Does the attached help.

It uses three helper columns on Sheet1 with a unique list of Dept & Colour on sheet 2.

I've had to truncate the size of the file and remove the last 5000 records in order to upload. I've also range valued the helper column formulae on sheet 1 and the SUMIF() formula on sheet 2 apart from the first three in M11:O11 and B2 on sheet2.

You could of course easily write a macro to create the helper columns and unique list with the SUMIF() formula on sheet2 if you want to automate the whole process.

[meezadahsra]

no no that is wrong. im sorry to say that but the output ie not what i was looking for.

for jl02356 it should be 2, that is the \color total. no summing is required. i know for sure this requires a macro.

[Richard Buttrey]

Hi,

What makes you think it requires a macro? You are mistaken if you think a macro is the only solution. See attached.

This is a fairly common type of request and they can generally be solved with standard Excel functionality - as in this case and where summing IS necessary.
It appears that the formula in B2 of sheet 2 had not been copied down, or maybe something went wrong when I had to truncate the file to upload it. Anyway the file is attached again and you can see that JL02356 in B1185 on sheet 2 does show the value 2. There may be more items on sheet 2 since I extracted the unique list of values before I truncated sheet 1.

The Sum is required since column O on sheet 1 identifies every occurrence of a unit count where there is an Item number in column G. So for instance CK00104 is associated with 5 items numbers which in total account for 7 units. Hence you see 7 in B2 on sheet 2

Always use standard Excel functionality before bothering with macros. It's far simpler and quicker.

[meezadahsra]

thanks !!!! awesome, indeed no need of a macro.....i thought it would require a macro because the problem seemed to not have a formulae in excel. but i stand corrected.....thanks again, ur great !!!

[Richard Buttrey]

Hi,

Glad it was of some use. As I intimated, parsing .prn files like this which originate from proprietary software is a common task. It goes without saying that it's preferable if the software that generates the data can export it in an Excel (or at least a CSV) format then that is preferable. But where you only have a .prn file the trick is to work out a standard Excel function which can identify the rows, (and only those rows) that are relevant to you. Knowing this then enables you to analyse the rows accordingly.

My favourite technique is to work out a formula in a helper column or columns and generate the word 'Keep' or 'Delete' depending on whether the row in question is a data row or just a page or banner header or other superfluous row. Then I just filter for the word 'Delete', in the helper column, select all the filtered rows and delete them and end up with a nice neat two dimensional table of data.