• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Arrange Row data into a proper list format

skyh3ck

skyh3ck

Member
Hello Friends

I had put an post to import multiple email message into one excel sheet, but i due to urgency i had to copy and paste around 260 emails into excel sheet manually.

Please see attache file, in sheet Raw data, i have data which i need to arrange in list format in List sheet, please help me how can i do this without wasting time on doing it manually.

in the last two coulmn i need date and product for testing, such as food, concrete, milk, water, etc.

thanks in advance
 

Attachments

  • JD Enquiries.xlsx
    46.2 KB · Views: 2
Skyh3ck: See attached.

I had to do about an hour of data scrubbing to get this to work; next time you need urgent help on short notice, please try to make sure that your data is consistent and error free.

All best.
 

Attachments

  • skyh3ck1.xlsx
    84.5 KB · Views: 2
Asheesh said:
Hi,

See the attached...
Click to expand...

Asheesh,

My formula solution is identical to yours, but if you check the results when compared with the raw data, you'll find that it creates mis-information: for example, the email address your formula returns for Caller No. 1 actually corresponds to Caller No. 4 in the raw data...It should return a null or blank or error value instead, because the source table does not have an email address for Records 1 through 3.

It's due to the inconsistencies in the source data; not the formula...but I don't know a good post-processing solution for that type of problem.

My best solution was to go back and scrub the raw data: time consuming, but perhaps the only way?
 
Last edited:
thanks Asheesh and eibi, i wish to have knowledge of excel like you guys are having, now i have to find out how i can correct that phone number error, for example most of the phone number has nuber like "9.19868E+11", how can i correct it to show correct phone number and also get only the date from the "Call Date & Time" heading

I need to prepare a small dashboard based on this, any idea i to make it more presentable to managers and directors
 
Hey eibi - that is a nice catch....I had not validated the data..I am now working on a solution to fix this...without manipulating the raw data..

skyh3ck - use the below formula to get the phone number for numbers like "9.19868E+11"

Suppose it is in G2 then the formula in J2 is

TEXT(G2,"0000000000")
 
You must log in or register to reply here.
Back
Top