select the tri repeat 4n

ADRIANA PACCOUNTING ASSISTANTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
The data for FAM TRI comes from column IZ and not column "BF", so I should be looking for matches in IZ, correct?
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin  

Data in FAM  TRI is matched again column BF

column  BF is the main source
Martin LissOlder than dirtCommented:
OK, but let's take row 19 as an example.

cell BF19 is 8879
cell IZ19 is 248379
the cells in JQ19 to KJ19 are 248 243 247 249      283      287      289      237      239      279      483      487      489      437      439      479      837      839      879      379

If I match against BF19 then none of the values in JQ19 to KJ19 would match, but if I matched against IZ19 then some of them would. So are you sure you want me to use BF?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin

So are you sure you want me to use BF?

yes !

needed

 1 week
 2 week
 3 week
4 week
Martin LissOlder than dirtCommented:
If I do that you will never get any matches.
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin

i saw matches  

for example

b2222.PNG
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
c111.PNG
Martin LissOlder than dirtCommented:
OK, you are right about that.

Here is something else though. In your pictures you show 883 being green in row 8 because 883 is in row 25, but week1 ends with row 20, correct?
Martin LissOlder than dirtCommented:
Also please show me where the violet color comes from.
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin


you rigth on that  

883 match is in second week  then should be pink
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin



d111.PNG
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin

violet color is for match in 4 week

in 3N we did only 3 weeks

but this 4N requiered an 4 week macth in violet color
Martin LissOlder than dirtCommented:
OK try this. Please check the results carefully because they don't agree with yours.
29090844.xlsb
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin is an great job

but why some don't show up

like  here

i see many  lines of FAM TRI  are completetly blank ( not match)

e11111.PNG
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
g111.PNG
Martin LissOlder than dirtCommented:
What are you saying is wrong in the second picture?
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin


BF row 188  have 00 at  2week but is not marked  


then

BF  row 192    have 057   2week but don't show marked  in
Martin LissOlder than dirtCommented:
See if this is better.
29090844a.xlsb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Great Job best expert
Martin LissOlder than dirtCommented:
I was wondering if you'd like the cell colors to different shades of the same color, like this.
2018-03-24_06-34-57.png
The color doesn't have to be green; it can be any color you like,
If the black font is too hard to read in the darker cells it can be made to be white, or any other color you like.
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin

im triying to add some data

but get this message

a1.PNG
1b.PNG
Martin LissOlder than dirtCommented:
What did you try to add and where did you add it?
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin

need add some years of data

i just copy and paste
Martin LissOlder than dirtCommented:
Please attach the workbook that you pated the data into.
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin LissOlder than dirtCommented:
Corrected.

On a different subject, I think that something needs to be done about the buttons because they pile up when columns are hidden and they sometimes get moved to places where they don't belong. One thing that could be done would be to remove them all and instead make it so that if for example cell KK2 were clicked, that the FAM DDD code would run, just as it does now when the FAM DDD button is clicked. This would not take a lot of work.
29090844b.xlsb
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin

great idea
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin

the problem still problem

when added more data


c111.PNG
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
FAM DDD is working great ! as i added more data
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
d11.PNG
Martin LissOlder than dirtCommented:
If you are still having a problem then please attach the workbook with the new data.
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin
Sorry !

the others workbook works as i i added  data with no problems

sorry i can't not post more data about this
Martin LissOlder than dirtCommented:
I'm confused. Do you have a problem?
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin

yes the problem is happening

but like i say

have no problems with the same workbook  i addd the data when you finish
and    never post the full data

this the only time i  added data and don't work when i paste

need get work the full range

because i always will add data  

i
Martin LissOlder than dirtCommented:
I can't fix the problem unless I have the workbook the data that's causing the problem.
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
e111.PNG
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
e111234255.png
Martin LissOlder than dirtCommented:
I can fix it but I need the workbook.
Martin LissOlder than dirtCommented:
The problem is NOT that you are adding new data. The problem is that the data contains a situation that the code isn’t expecting.
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin LissOlder than dirtCommented:
The workbook you attached does not include the correction that I made in 29090844b.xlsb above. I'll fix it again in this workbook.
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin

sorry i try always have the last


but maybe i mix  unintentionally
Martin LissOlder than dirtCommented:
I found the problem. When you click on any date in column B up to row 4189 you'll see something like 2/24/2018. However if you click on any of the cells in column B in rows 4191 to 4215 you see something like Monday, March 05, 2018. Ill see what I can do about that.
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
;)
Martin LissOlder than dirtCommented:
I'm stumped for now. The best thing to do is for you to convert the dates in rows 4191 to 4215 to the m/dd/yyyy format. Once you do that it should work until you get to row 4233 at which point there are no dates at all.
Martin LissOlder than dirtCommented:
OK, this works. I did three things:
  • I added code so that improperly entered dates like those in rows 4191 to 4215 won't cause errors.
  • I added code which does not attempt to find matches in the following 4 weeks if the date in column "B" is blank like it is in rows 4233 and below. In other words you won't see any colors in those rows.
  • Since the Fam Tri process takes a while to run, I added a progress indicator in the Status Bar at the bottom of the screen that is updated each time 250 rows are processed. It looks like this picture.
2018-03-25_00-08-45.png29090844c.xlsb
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin

i still get this mesage

a1.PNG
a2.PNG
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin LissOlder than dirtCommented:
You have weekdays in rows 4229 to 4281 but no other data for those rows. If you delete those rows then it will be OK.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.