Excel Assistance

Forums › Microsoft Windows › Excel Assistance

networkn

Networkn
32876 posts

Uber Geek
+1 received by user: 15477

ID Verified

Trusted

Lifetime subscriber

#208187 30-Jan-2017 22:31

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?

1 | 2

3084 posts

Uber Geek
+1 received by user: 483

#1713020 30-Jan-2017 22:43

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.

networkn

Networkn
32876 posts

Uber Geek
+1 received by user: 15477

ID Verified

Trusted

Lifetime subscriber

#1713022 30-Jan-2017 22:54

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.

darylblake

1172 posts

Uber Geek
+1 received by user: 410

Trusted

#1713023 30-Jan-2017 22:58

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.

tardtasticx

3084 posts

Uber Geek
+1 received by user: 483

#1713030 30-Jan-2017 23:12

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

networkn

Networkn
32876 posts

Uber Geek
+1 received by user: 15477

ID Verified

Trusted

Lifetime subscriber

#1713035 30-Jan-2017 23:17

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.

Jaxson

8172 posts

Uber Geek
+1 received by user: 1332

Trusted

#1713060 31-Jan-2017 07:59

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.

Apple TV

Stream your favourite shows now on Apple TV (affiliate link).

ObidiahSlope

260 posts

Ultimate Geek
+1 received by user: 66

#1713135 31-Jan-2017 10:07

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

MikeAqua

8033 posts

Uber Geek
+1 received by user: 3823

#1713140 31-Jan-2017 10:17

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

KrazyKid

1247 posts

Uber Geek
+1 received by user: 616

#1713154 31-Jan-2017 10:41

Chuck a filter on the data.
Filter for what words etc you want.
Highlight the lines in bulk (by selecting them all at once)
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

networkn

Networkn
32876 posts

Uber Geek
+1 received by user: 15477

ID Verified

Trusted

Lifetime subscriber

#1713206 31-Jan-2017 11:17

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.

MikeAqua

8033 posts

Uber Geek
+1 received by user: 3823

#1713881 1-Feb-2017 12:44

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

Mike

AliExpress

Shop now on AliExpress (affiliate link).

networkn

Networkn
32876 posts

Uber Geek
+1 received by user: 15477

ID Verified

Trusted

Lifetime subscriber

#1713888 1-Feb-2017 12:55

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.

andrew027

1286 posts

Uber Geek
+1 received by user: 557

#1714208 1-Feb-2017 21:52

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

networkn

Networkn
32876 posts

Uber Geek
+1 received by user: 15477

ID Verified

Trusted

Lifetime subscriber

#1714249 2-Feb-2017 00:08

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?

ObidiahSlope

260 posts

Ultimate Geek
+1 received by user: 66

#1714250 2-Feb-2017 00:23

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