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
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
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:
If there is commonality between the sources (complete or perhaps even partial) a macro or macros could be written to do the restructuring.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
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?
0
Martin LissOlder than dirtCommented:
In your ExcelSample_ActualvsDesireable.png, is it the "More Desirable" what you want to see after importing the CSV?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Martin LissOlder than dirtCommented:
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.
0
Martin LissOlder than dirtCommented:
Click the "Open CSV" button.
29092242.xlsm
0

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
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
That is so cool. I appreciate that very much sir thank you.
0
Martin LissOlder than dirtCommented:
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
0
Martin LissOlder than dirtCommented:
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

0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
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.
0
Martin LissOlder than dirtCommented:
You didn't add the link, but no problem because when you ask the new question I'll probably see it.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
0
Martin LissOlder than dirtCommented:
That link points to something that was resolved last December. Is that correct?
0
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
Microsoft Office

From novice to tech pro — start learning today.