Solved

Import data into sheet, change layout, insert date stamp and create macro button to do this

Posted on 2014-11-27
55
93 Views
Last Modified: 2014-12-13
Hi
I have an extract from a third party piece of software that dumps its data after a process has ran to a csv file.
I now have a macro that imports the data into my results book and cleans it up, this next process would have to be part of this macro in order for the correct results to turn up on the results page after import.

What I need now is a macro to move the data from my import sheet (called Historical data) to my results sheet, upon moving these in to the sheet, I need the macro to put my data into column format rather than in row format, inserting all new data in the top cell. During this process, I will need a time and a date stamp adding to the imported row. I have created a table to illustrate which columns are required from the Historical data sheet.
Ideally I would like the import of the csv macro and this macro to be a single click button on the results sheet.


Once the data is in this new table, conditional formatting will need to be applied to highlight a pass or fail according to variables, all set up on the variables page
Results-V02.xlsm
0
Comment
Question by:RPUKsupport
  • 32
  • 23
55 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40469149
Sorry but a bit confused on what is needed:


I have created a table to illustrate which columns are required from the Historical data sheet.

yes I can see that but you also have:

1) In historical Data items that need to be cleared and deleted like blank, Stadard1 etc,,,
2) So many explanations and variables in both sheets results and variables that one would spend a day gathering all this to simply understand the whole concept.
3) a layout in sheet result that is not quite obvious that one has to look very closely to understand that the element goes in A then macro stamp a date and time then there are elements that comes in Columns ... to soon notice that it is the same sequence all the time ....

etc.. and more

Let agree !!!

These notes are fine they are good for you and would maybe help to develop at some time BUT
You need to advise step by step what you want as you cannot assume we are mind readers and sure don't understand your business like you do.

So I suggest:
1) You make up a sheet Historical data that is clean with only some data (no red and no undesirables)
2) In sheet Result you put some examples related to the first data that is there how you want it
and an other example
and explain in detail what we need to put where and how variables links with the amounts that you are putting.

gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40469237
ok no problem, I will work on this tonight
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40470121
I am waiting for your comment as have already the interface ready but without the interaction with the variables as it is not at all clear to me on how to have it interact.

For you to understand, as soon as you import the csv it will upload all the data that is newly imported in the Result table as per the columns you already have there this is where I am now.

Pls advise.
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40470127
Hi, see attached.
I am just checking on the STWL 0.05 variable of plus or minus. I think it may only be on one element
Results-V02-Complete-Example-1.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40470135
Well this is done already but what is the variables have to do with this !!!????
Sorry you need to explain the interaction of variables we are chasing our tail and wasting time.
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40470173
I have just been through the process again and found out some more information I was not told before.

there are many different tests performed and then exported to the csv. the csv is then imported into our sheet. there are 2, not 1 test that have to pass, in order for all others to pass.
STWL 0.05 and BK (I didn't know this one existed but apparently its a new edition)
STWL 0.05 and BK have a variable range attached to them, where as all other tests only have a warning (internal) and a max (permitted)
The STWL 0.05 sample, all tests have to be within the element variable plus or minus cell b7 (element cells b5 c5 d5 e5 f5 g5 h5 on the variables sheet),
The BW sample, all tests have to be within the element variable plus or minus cell b8 (element cells b5 c5 d5 e5 f5 g5 h5 on the variables sheet),
These 2 must present a Pass for any other sample to pass (STWL 0.05 and BK)

Then, all other samples, must be below the permitted and internal ranges (variables sheet, cells b3 b4 etc) and the total of all the elements must be less than variable cell b9.

Present pass cell colour in green, above internal tolerance levels in red and above permitted in orange


I hope this helps, im probably not explaining it very well
Results-V02-Complete-Example-1.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40470808
Could you please post an csv that have all the Samples you are mentioning here so writer knows what you are talking about ?

at least to have
STWL 0.05
BK
BW sample

so that we can do proper coding and testing ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40472299
Any news ? The part is ready but need to make sure about the new items pls post a csv as per previous request.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40472598
Try this file.

Once you load an csv it will automatically upload results in sheet Results. Pls note for testing purposes I have created in Variables down below 2 items that have borderline values to make sure Red and Orange works well. You you want to test it just copy values that are in Permitte under Permitted and Interna under Internal and check the results.

Let me know.
I am sure it need fine-tuning but will wait for your comments as did not have more than the original file you had posted.
Results-V03.xlsm
0
 

Author Comment

by:RPUKsupport
ID: 40473200
I will have this tested this morning and let you know
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40473293
ok fine
0
 

Author Comment

by:RPUKsupport
ID: 40473426
Its beginning to take shape and looking good.
I have picked up on a few things
On Friday I sent an updated version of the sheet, with some additional variables in.
I have attached them, see variables sheet.

The results of the BK sample should only be a number that is equal to (variable number for element located in variables sheet, cell b14 onwards) then plus or minus the variable in cell b15.

All samples can only produce a pass if BK and STWL 0.05 produce a Pass. If one of these is a Fail, all will be issues with Fail. At the moment, samples produce a pass, even if BK and STWL have failed. Can this be corrected?

When the sheet is updated, can the information on the Results sheet insert the imported data at the top and move all previous results down the page?

Note: I am quoting cell numbers just for illustration purposes, you can move them around so that the sheet looks better

I have enclosed a new export file that should produce a Fail for all results, as BK and STWL have one or 2 results that are above the threshold.
Export.csv
Results-V03.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40473480
ok fine let me look at all this.
You mean to say even if an item is pass at the end if either BK of STWL is a fail then we should go back on all items and flag them as Fail ? Is that what you want ?
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40473481
That is exactly what I mean, if either one or even both fail, then all other results that have just been imported will be a fail
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40473525
ok fine but may I give a humble point of view ?

As you have passes for each item and if an item has Pass why don't you keep it as-is and if BK or STWL Fail then it will show as Fail and for sure in your businesss you know well that if one of these 2 items is a fail then the whole lot is is Fail. Why not keep it like this ?

Just curious.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40473532
One more if BK has same Variables as STWL can we create BK in Variables this would make it at least consistent ?
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40473555
Yes, it does sound strange, but the STWL and BK are like a base, if that fails then nothing else should pass, as its base has failed, that's the thinking behind that one.
Please create BK as its own variables, I believe that this is going to be 0 for the main part, plus or minus 0.01. STWL will be within 0.07. these have to be variables as they tend to change a couple of times per year
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40473570

Please create BK as its own variables, I believe that this is going to be 0 for the main part, plus or minus 0.01. STWL will be within 0.07. these have to be variables as they tend to change a couple of times per year

Not sure I understand what you mean please create BK and attach the file thank you.
the rest is done except for this b7 to add not sure if it is on each and every item we should add b7 you have it + or - please provide a clear example.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40473802
ok try this version.

1) Incorporated BK as a variable where values are same as STWL
2) If BK or STWL Fail the whole lot Fails
3) Each item is compared + (PlusorMinus figure) with the variable this means a value for STWL = 0.075 in the csv will result in a Fail as it will be compared like follows:
for the Permitted  0.075+0.007 = 0.082 so when it compare value in csv 0.075 to 0.082 it pass
however
for the Permitted 0.075-0.007 = 0.068 so when it compare value in csv 0.075 to 0.068 then it fails
and in this case this is why this item shows now Fail where as before we were not checking the PlusorMinus figure it was Pass.

Pls advise if logic is correct.
gowflow
Results-V04.xlsm
0
 

Author Comment

by:RPUKsupport
ID: 40475644
Sorry for the delay, awaiting feedback.
One thing I have found out is that BK does have a different variable plus or minus, than STWL. STWL is 0.07 and BK is 0.1.
All the variables for BK and STWL I have included in the re-attached sheet.

I have had 2 requests for changes as well, but we can leave that if you wish until this one is sorted
Results-V04.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40475706

I have had 2 requests for changes as well, but we can leave that if you wish until this one is sorted

What do you mean ?

More for BK can't you have it
BK      0.1      0.1      0.1      0.1      0.1      0.1      0.1      0.7
without the PlusorMinusBK then if all is 0 ??? this is getting too annoying to have so many exceptions.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40475835
Here it is. I kept your BK = 0 and introduced a new variable PlusorMinusBK at 0.1

run it and check if the results are ok.
gowflow
Results-V05.xlsm
0
 

Author Comment

by:RPUKsupport
ID: 40476217
As you can see from the imports already in there, the BK is always in orange which indicates a fail, yet the results are no less than 0.1 which would indicate a pass.

I have also imported some results with very high values in. The value itself (say the Zn column) was 10, this should have flagged red but didn't, it was green. The Total did flag as red so that's all ok

The additional requests are only can we have the import button on the results page instead of the historical data page
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40476259
ok for the button no sweat but from all your comment I cannot tell if there is a problem or not. It has to be 1000% correct or else you need to tell me where it is not so we correct it.

I don't understand this:
I have also imported some results with very high values in. The value itself (say the Zn column) was 10, this should have flagged red but didn't, it was green. The Total did flag as red so that's all ok

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40476305
for sure it will not give the correct results as you failed again to explain to me by an example what do you mean by + or - 0.07 or 0.1

I gave an example before but you didn't even bother commenting and explaining.

Sorry cannot continue like this this is a total waste of time.

Unless you comment clearly on ID: 40473802 and explain by example the plus or minus how it should be treated, not willing to give this 1 more minute.

gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40476377
Ok, I will try to explain.

BK, this is like a blank slate, all results should be as close to 0 as possible (0 being the variable number) with a + or - of 0.1, so the highest the number can reach is 0.1, any more would be a Fail and highlight in red and the lowest is -0.1, any less than this will be a fail, highlighted in red.

STWL 0.05, this is like a calibration, results should be close to 0.05 as possible (0.05 being the variable number this time) with a + or - of 0.07, so the highest the number can reach is 0.12, any more would be a Fail and highlight in red, the lowest is -0.02, any less than this will be a fail, highlighted in red.

If the blank slate and calibrations fail, all the tests should also be a fail (as we have working) as all the results will be inaccurate

All other results come out fine, they are the actual tests.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40476421
ok fine.

but we are also adding for all other solutions apart from STWL and BK the +0.07 and -0.07 when we compare each Permited and Internal as you had in the variable fine is that correct ???

gowflow
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:RPUKsupport
ID: 40476445
Im thinking yes
STWL has the +0.07 and - 0.07 and the BK has the +0.1 and -0.1
All other results do not need the + or - 0.07 or 0.1, they are totally independent of this.
As you say, for the results of all other samples and solutions, if the number is higher than internal it is red (fail), and if it is higher than permitted (fail), it is orange
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40476506
ok fine then your initial advise was wrong in respect of the other items.

Now I just noticed why we have an issue with STWL if you notice you had 2 csv one of them has
STWL 0.05
and the other one has
STWL0.05

note the space in the first and no space in the second reason why it was not finding the variable as it was coded
STWL 0.05

I have overcome this by simply removing the 0.05 and now we have in Variables
STWL
BK

the rest I am fixing now.
will revert shortly.
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40476517
thank you. Sorry I didn't check the data
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40476571
Don't worry I checked it !!! when I chk why results are wrong then it came out.

Ok I hope that we nailed it in this version pls chk. (Important to keep the denominations in Variable as they are now only you can change values but not labels or else the whole thing does not work.

gowflow
Results-V06.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40480754
Any news ?
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40480789
I literally just logged on.
tested all yesterday, everything ok,  apart from one thing, the total on the STWL is always a fail on the total column, the total can equal up to 2 (on variables page under total) but it is failing at 0.05 plus 0.07 or greater.

So, can you take the total for SWTL to equal the total on the variables page, I presume the same will be for BK.

All totals fields should be no higher than the "Total" variable

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40481156
Give me 1 sec I will look at this. Although you had this Total = 2 I did not take it in consideration as thought that we need to check Total against the Total in Variables Would that be fine if we do it this way ???

or let me get this right: If we put for Permitted under Total column 2 and Internal also 2 and STWL 2 and BK 2 would this be ok ???

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40481221
Check this one out.

I would appreciate for you to make my life easier is to give me the results:
Ie on the data this is in Historical what should be the colors !! I am talking about the file you last posted Export.csv

This would save both of us spinning around.

gowflow
Results-V07.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40486548
It would help if the exchanges were not from week to week but rather faster so as to accomplish what you need and also contribute to a more productive and efficient exchange.
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40488366
I apologise, I now have the login details for the account.
Enclosed is a copy of lost of data, export.csv. I have imported this into results 06 and results 07.
on results 07, I have added some comments beside the imported data, from the looks of things it just needs tweaking, it should be evident what is required from the comments. Also, the import button requires user to choose file location on Results07
Please let me know if you require more info
Export.csv
Results-V07.xlsm
Results-V06.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40488742
ok lets cut it short or else will be chasing our tail here is the logic I understood from you and applied please correct where it needs to be corrected.

This is what we have in Variables.
STWL      0.05       0.05           0.05       0.05       0.05       0.05       0.05
BK                   0                   0         0           0                   0                   0                   0
PlusorMinusBK      0.1                                    
PlusorMinus          0.007

The logic Says:
If the item is
1) STWL then
if the value  bigger than 0.05+0.007 or value smaller than -(0.05+0.007) then RED
else if value is between (0.05 and 0.05+0.07) or value is between (-0.05 and -(0.05+0.007)) then ORANGE
else GREEN

2) BK same as 1 but will put the figures
if the value  bigger than 0+0.1 or value smaller than -(0+0.1) then RED
else if value is between (0 and 0+0.1) or value is between (-0 and -(0.1)) then ORANGE
else GREEN

3) For all other items
If value bigger than Permitted then RED
else if value between Internal and Permitted then ORANGE
else GREEN

Please check VERY well each case and let me know. I think we are having problem with the way you are setting your variables. and if the logic is wrong then please let me know in each case where.

for the file requesting input it is very simple the variable should always be 'ImportFileLocation' I usually put and 's' at the end so the routine request a file for my test as I do not have your network pass so I may have forgot to remove the 's' if it happens in the future make sure you always have 'ImportFileLocation'

Let me know. and please as fast as possible.
gowflow
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40488819
Ok, here we go, my comments below :

"1) STWL then
 if the value  bigger than 0.05+0.007 or value smaller than -(0.05+0.007) then RED
 else if value is between (0.05 and 0.05+0.07) or value is between (-0.05 and -(0.05+0.007)) then ORANGE
 else GREEN"

Reply 1
So, how I understand it, we have 0.05 as a starter, then either side of that we have plus or minus 0.07, so anything greater than 0.05+0.07 (0.12) and anything less than 0.05-0.07 (-0.02) then RED.
I think we should take the next line out (ORANGE) for the STWL and BK tests as it is getting confusing
So, if the number is between 0.12 and -0.02 then GREEN

 2) BK same as 1 but will put the figures
 if the value  bigger than 0+0.1 or value smaller than -(0+0.1) then RED
 else if value is between (0 and 0+0.1) or value is between (-0 and -(0.1)) then ORANGE
 else GREEN

Reply 2
We have 0 as a starter, then either side of that we have plus or minus 0.1, so anything greater than 0+0.1 (0.1) and anything less than 0-0.1 (-0.1) then RED.
Again we should take the next line out (ORANGE) for the STWL and BK tests as it is getting confusing
So, if the number is between 0.1 and -0.1 then GREEN

 3) For all other items
 If value bigger than Permitted then RED
 else if value between Internal and Permitted then ORANGE
 else GREEN

Reply 3
Correct!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40488850
I am ok with everything except this:

anything greater than 0.05+0.07 (0.12) and anything less than 0.05-0.07 (-0.02) then RED.

if we do: 0.05-0.07= 0.043 and NOT 0.02 !!! how did you come out with 0.02 ??

gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40488909
I stand corrected, I missed out a 0, thank you.
So......

Reply 1
 So, how I understand it, we have 0.05 as a starter, then either side of that we have plus or minus 0.07, so anything greater than 0.05+0.007 (0.057) and anything less than 0.05-0.007 0.043) then RED.
 I think we should take the next line out (ORANGE) for the STWL and BK tests as it is getting confusing
 So, if the number is between 0.057 and 0.043 then GREEN

  2) BK same as 1 but will put the figures
  if the value  bigger than 0+0.1 or value smaller than -(0+0.1) then RED
  else if value is between (0 and 0+0.1) or value is between (-0 and -(0.1)) then ORANGE
  else GREEN

 Reply 2
 We have 0 as a starter, then either side of that we have plus or minus 0.1, so anything greater than 0+0.1 (0.1) and anything less than 0-0.1 (-0.1) then RED.
 Again we should take the next line out (ORANGE) for the STWL and BK tests as it is getting confusing
 So, if the number is between 0.1 and -0.1 then GREEN

  3) For all other items
  If value bigger than Permitted then RED
  else if value between Internal and Permitted then ORANGE
  else GREEN
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40488934
I agree BUT
you will need to change your variables like I thought so for BK as it is inconsistent with the logic.
BK should be all the way 0.1 0.1 0.1 etc....
and PlusorMinusBK should be 0

this way all will work fine or else you will always get RED for BK even though it is in range as when checking the total then it will not balance anymore.

Anyway let talk little and let you check this version. I voluntarily left a blank row on row 12 so you can compare the results with mine. I changed the variable for BK. and put notes on the results you missed 2 !!

Let me know.
gowflow
Results-V08.xlsm
0
 

Author Comment

by:RPUKsupport
ID: 40489148
I think I can see how it works now.
I have enclosed the sheet with some working, there appears to be one problem that I can see. I have changed some variables to see if it fails and passes. So far so good

Enclosed is the export.csv as well that I have been using
EXPORT.CSV
Results-V08.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40489389
Please let me know what is the problem as seem gloomy and not clear.
I hope will get done with it as start loosing patience.
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40489593
Hello, well I think we are nearly there.
Column M15 and M19 on the results sheet of results08 attached in my last post, the total is higher than the variables so should appear in red, I highlighted one further down column M as well in blue. As the total is over 10 for this column it should be a fail (RED), as the internal level is 0.85 and the permitted is 2.
That's the only thing that I can
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40490045
Well I don't know what happened but it seems a stupid error has slipped and seems that you did not noticed it. For some reason the values showing in Result were picking the data from Col E in historical whereas it should have picked Col L. The problem is the inconsistency as it was showing the results of Col E but doing the calculation based on Col L which was giving all these weird results.

I am sorry for that don't really know what happens.
Pls check this version and let me know. If you then explain what should be orange for STWL and BK maybe I can then re-introduce it and it would make sense.

Let me know
gowflow
Results-V09.xlsm
0
 

Author Comment

by:RPUKsupport
ID: 40491791
Hi, I have it on test, so far its looking good.
I think we should not use or bother about the Orange part for the STWL and BK samples, as it makes no sense to have them so see how they go without it.
I will test for another day and then if all is good we can say its completed
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40491972
ok fine let me know. pls don't disappear I need to get done with this or else it is v difficult to remember what we did when some time passes.
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40493337
ok, I should have a good response in the next few hours
0
 

Author Comment

by:RPUKsupport
ID: 40494116
Hi,
Please see attached. I have enclosed an export with the data in also
Check STWL 0.05 column M rows 12 and 21, should equal fail but equal pass. threshold would be 0.043 (0.05 - 0.007)
Also, once corrected, just need the Import CSV button putting back so that its automatic

many thanks
EXPORT.CSV
RESULTS09.xlsm
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40494556
ok done I have put back your BK to all 0 and PlusorMinusBK to 0.1

I hope this time we nailed it.
gowflow
Results-V10.xlsm
0
 

Author Comment

by:RPUKsupport
ID: 40495165
Gowflow.......
I think you have cracked it, i have tested a few results and from what ive done so far it looks good.
I will put it into production and see what the results are like tomorrow. :)
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40496659
ok fine let me know.
gowflow
0
 

Author Closing Comment

by:RPUKsupport
ID: 40496804
Gowflow
Thankyou for your time and patience in helping me with this. it is appreciated. Works very well and gets the information i desired.

Again many thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40497821
Glad we finally nailed it. Pls let me know if you need further help in any other issue by posting a link in here. I will keep this monitored.
gowflow
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now