asked on

Tools Tricks or Tips in manipulating data in Excel to get scrambled data in order

I have been wondering what is possible with data when it comes to you in a variety of ways. I am hoping to learn some tricks and advice as the experts might know how to deal with it?

I copied a set of records from the Dell website as an example. So this data is just a generalization of what I might get back from a number of sources. It is not the only way I get data handed to me.

The question:
If I get the data in a format like the file ExcelDataSample_DataAsIs but would like to put it in a more manageable format like what is visible in ExcelSample_ActualvsDesireable
What would be the best option(s) The only ones I "think might" be able to do it is. YOU MIGHT WANT TO LOOK AT THIS FIRST...... ExcelSample_ActualvsDesireable

Formula or Function that may or may not be able to format it correctly in positions that can be managed like a table.
A Macro that can order the list but the length (or number of items) transitioning from one to another is different for each item in the list.
Some kind of an import / export trick I probably do not know if that is even possible though.

ExcelSample_DataAsIs_CSVFormat---She.csv
ExcelSample_ActualvsDesireable.png
ExcelSample_DataAsIs.png

SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RUA Volunteer2?

ASKER

Any ideas where there might be examples of Macros for my needs. My problem is I do not know often where to start in situations like this. If it is macros are there specific features of a Macro I would need to know how to do as well as maybe an order in doing them?

Martin Liss

In your ExcelSample_ActualvsDesireable.png, is it the "More Desirable" what you want to see after importing the CSV?

Martin Liss

If the answer to my question about commonality between input sources is "yes, they are similar" then I will write (and document) the macro for you.

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RUA Volunteer2?

ASKER

That is so cool. I appreciate that very much sir thank you.

Martin Liss

If you have any questions about the code, then feel free to ask in this thread any time.

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017

Martin Liss

Oh, I meant to mention that the code assumes that there are only two values in the CSV file that indicate the results of a test and they are "Passed" and "Failed". I took a guess that the second one was possible. If it's not one of those two than that item and the results for that item won't show up.

To change that then change this line

If Cells(lngRow, "A") = "Passed" Or Cells(lngRow, "A") = "Failed" Then

to

If Cells(lngRow, "A") = "Passed" Or Cells(lngRow, "A") = "Failed" Or Cells(lngRow, "A") = "some other value" Then.

or replace this

   If Cells(lngRow, "A") = "Passed" Or Cells(lngRow, "A") = "Failed" Then
       Cells(lngResRow, "B") = Cells(lngRow - 2, "A")
       Cells(lngResRow, "C") = Cells(lngRow - 1, "A")
       Cells(lngResRow, "D") = Cells(lngRow, "A")
       lngResRow = lngResRow + 1
   End If

Open in new window

with this.

    Select Case Cells(lngRow, "A")
        Case "Passed", "Failed", "other 1", "other 2"
            Cells(lngResRow, "B") = Cells(lngRow - 2, "A")
            Cells(lngResRow, "C") = Cells(lngRow - 1, "A")
            Cells(lngResRow, "D") = Cells(lngRow, "A")
            lngResRow = lngResRow + 1
    End Select

Open in new window

RUA Volunteer2?

ASKER

That code was helpful and really good for me to know right now. Learning all I can on best practices for developing some software that is very useful.
I may ask you for some advice soon about something I want to develop to challenge myself. Want to get things started not sure how to plan for it. Here is the link to that question and again your support on this question was much appreciated.

Martin Liss

You didn't add the link, but no problem because when you ask the new question I'll probably see it.

RUA Volunteer2?

ASKER

Link to Development Idea

Martin Liss

That link points to something that was resolved last December. Is that correct?