Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.




20020 posts

Uber Geek
+1 received by user: 6097

Trusted
Lifetime subscriber

# 208187 30-Jan-2017 22:31
Send private message

Hi. 

 

 

 

We are about to go to the Disputes Tribunal to deal with a particularly predatory scumbag. 

 

I have kept all the texts and have backed them up in xml format. I found a reader which could then export just 1 contacts sms messages, to a text file. 

 

I now have a 350 line text file which is times dates, messages each to a new line. 

 

I'd like to find a way to colour code lines sent and lines received in different colours to make it easier to read. 

 

I am too tired tonight to spend hours trying to figure it out, does anyone have an easy answer?

 

 


View this topic in a long page with up to 500 replies per page Create new topic
 1 | 2
2924 posts

Uber Geek
+1 received by user: 400


  # 1713020 30-Jan-2017 22:43
Send private message

Use a conditional format based on sender/receiver will be your best bet. Like, if sender = +6421123456 highlight red

Without knowing the contents of the file it would be hard to give detailed instructions, this should offer some guidance tho:
https://www.pryor.com/blog/using-ifthen-in-conditional-formatting-in-excel/

Apologies for formatting, mobile post.




20020 posts

Uber Geek
+1 received by user: 6097

Trusted
Lifetime subscriber

  # 1713022 30-Jan-2017 22:54
Send private message

Yeah I actually found that 10 minutes ago, but it's a little more tricky unfortunately. I was going to do it conditional on the word sent or received but those words are in the body to and after sent/received are changing dates. Also, that seems to only highlight the first column not everything. 

 

I may just need to send it to them in plain text. Each is a line so it's not rocket science but just trying to make it easier on them.

 

 


 
 
 
 


943 posts

Ultimate Geek
+1 received by user: 310

Trusted

  # 1713023 30-Jan-2017 22:58
Send private message

Edit in a regular text editor. 

 

Find/Replace is a good start. You can replace newline characters etc XML is particularly easy to work with as you can look for patterns and replace on them.

 

 






2924 posts

Uber Geek
+1 received by user: 400


  # 1713030 30-Jan-2017 23:12
Send private message

Give this one a try: https://www.ablebits.com/office-addins-blog/2013/10/29/excel-change-row-background-color/

 

 

 

Looks to be perfect for this situation where you can specify a column for it to search, if any of the cells in that column meet the condition it'll format the whole row. 

 

E.g. in your case the formula to find cells in column D that contain "sent" would be: =SEARCH("sent", $DX)>0 where X is the number of the cell in row D that you want to base the formatting off (I believe this is how that works. I don't have Excel on me ATM so couldn't test sorry). The $ is the crucial part as that restricts the search to only column D. That way it won't look for "sent" in for example the contents of the message. 

 

 

 

 

 

Otherwise if you don't feel like playing around with Excel just send it as is. I think people can read between the lines and figure out what's going on as needed. 
If you're taking this with you to the disputes tribunal just make notes of the row for important messages so they can be easily found, you wouldn't want to be sifting through 300+ lines to find a specific text as I imagine the person overseeing it wouldn't be too impressed. From what I recall when I did this (car crash, insurance company made us go) they want info ready at a moments notice so be organised and know where everything important is. Good luck! :-D




20020 posts

Uber Geek
+1 received by user: 6097

Trusted
Lifetime subscriber

  # 1713035 30-Jan-2017 23:17
Send private message

Thanks. I don't expect to need much evidence. We hired a guy to do some paving and after 5 months of excuses we sacked him (it's a much longer story). The people who "fixed it" did the entire job in 1.5 days for half the money.


6886 posts

Uber Geek
+1 received by user: 654

Trusted

  # 1713060 31-Jan-2017 07:59
Send private message

networkn:

Thanks. I don't expect to need much evidence. We hired a guy to do some paving and after 5 months of excuses we sacked him (it's a much longer story). The people who "fixed it" did the entire job in 1.5 days for half the money.



Good luck mate, hope it goes well for you.

220 posts

Master Geek
+1 received by user: 35


  # 1713135 31-Jan-2017 10:07
Send private message

Windows Powershell has XML maipulating tools. This site has a good example introduction;

 

 

 

http://www.tomsitpro.com/articles/powershell-read-xml-files,2-895.html

 

 

 

 





Obsequious hypocrite

5376 posts

Uber Geek
+1 received by user: 2201


  # 1713140 31-Jan-2017 10:17
Send private message

The conditional formatting can be used in two ways that will help you here: -

 

1) Can be set up to format the entire row based on one cell in that row

 

2) has a text condition called contains.

 

E.g. Set all rows to red. Then set the conditional formatting to look for the word 'sent' in the Body cell and colour the whole row green accordingly.





Mike

719 posts

Ultimate Geek
+1 received by user: 208


  # 1713154 31-Jan-2017 10:41
Send private message

     

  1. Chuck a filter on the data.
  2. Filter for what words etc you want.
  3. Highlight the lines in bulk (by selecting them all at once)
  4. Repeat for different colours.

 

When the filter is removed the lines filtered out will not be highlighted.

 

Its a manual process but could be faster in the end deoending on exactly what you are doing

 

 




20020 posts

Uber Geek
+1 received by user: 6097

Trusted
Lifetime subscriber

  # 1713206 31-Jan-2017 11:17
Send private message

The issue I am having now, is that I can't get excel to import the data properly, into 2 columns, one for date and one for data, I've tried everything I can, and it's not liking it. There are no suitable delimiters either which is challenging. I'm gonna contact the DT and see what they want.

 

 


5376 posts

Uber Geek
+1 received by user: 2201


  # 1713881 1-Feb-2017 12:44
Send private message

Can you use a find and replace function to insert suitable delimiters?





Mike



20020 posts

Uber Geek
+1 received by user: 6097

Trusted
Lifetime subscriber

  # 1713888 1-Feb-2017 12:55
Send private message

MikeAqua:

 

Can you use a find and replace function to insert suitable delimiters?

 

 

Not a bad idea, not sure why I didn't think of it. Let me check it out. 


1255 posts

Uber Geek
+1 received by user: 533


  # 1714208 1-Feb-2017 21:52
Send private message

I'd be happy to help out. Can you provide a sample of what one record looks like in the XML file?




20020 posts

Uber Geek
+1 received by user: 6097

Trusted
Lifetime subscriber

  # 1714249 2-Feb-2017 00:08
Send private message

Ok so I made some progress and managed to get the data exported to text file, then search and replaced so I added a delimter so I have excel down to 2 columns, 1 containing the date and the word sent/received depending on what it was, and the other column which contains the actual text. 

 

Now it's in excel, I have tried to select all and then do conditional formatting based on sent: or received: but it will only format column 1 not the actual text data as well. I need it to highlight the entire row in a specific colour, not just 1 column.

 

 

 

Any ideas?

 

 


220 posts

Master Geek
+1 received by user: 35


  # 1714250 2-Feb-2017 00:23
Send private message

Any ideas?

 

 

 

 

Format your data as a table. Once incorporated into a table choose a table style

 

 

 

https://support.office.com/en-us/article/Format-an-Excel-table-6789619f-c889-495c-99c2-2f971c0e2370





Obsequious hypocrite

 1 | 2
View this topic in a long page with up to 500 replies per page Create new topic



Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

HPE to acquire supercomputing leader Cray
Posted 20-May-2019 11:07


Techweek starting around NZ today
Posted 20-May-2019 09:52


Porirua City Council first to adopt new council software solution Datascape
Posted 15-May-2019 12:00


New survey provides insight into schools' technology challenges and plans
Posted 15-May-2019 09:30


Apple Music now available on Alexa devices in Australia and New Zealand
Posted 15-May-2019 09:11


Make a stand against cyberbullying this Pink Shirt Day
Posted 14-May-2019 20:23


Samsung first TV manufacturer to launch the Apple TV App and Airplay 2
Posted 14-May-2019 20:11


Vodafone New Zealand sold
Posted 14-May-2019 07:25


Kordia boosts cloud performance with locally-hosted Microsoft Azure ExpressRoute
Posted 8-May-2019 10:25


Microsoft Azure ExpressRoute in New Zealand opens up faster, more secure internet for Kiwi businesses
Posted 8-May-2019 09:39


Vocus Communications to deliver Microsoft Azure Cloud Solutions through Azure ExpressRoute
Posted 8-May-2019 09:25


Independent NZ feature film #statusPending to premiere during WLG-X
Posted 6-May-2019 22:13


The ultimate dog photoshoot with Nokia 9 PureView #ForgottenDogsofInstagram
Posted 6-May-2019 09:41


Nokia 9 PureView available in New Zealand
Posted 6-May-2019 09:06


Motorola Solutions joins local partners to deliver advanced communications network in New Zealand
Posted 30-Apr-2019 21:50



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.