0

I have a dataset that I want to analyse, but it is in quite a strange format. See below:

> Account_Details         Account_Name 
> account # 1             client name 
> product 1 name          product 1 value          
> product 2 name          product 2 value           
> product 3 name          product 3 value    
> account # 2             client name
> product 3               product 3 value
> product 2 name          product 2 value
> account # 3             client name
> product 6 name          product 6 value

Is there a way that I can sort these to get a view as seen here:

Account Number       Product 1     Product 2    Product 3...                
account # 1            1234.1        346.2        10154.36
account # 2            0             556.6        6154.63
account # 3            123.04        905.24       101.16
.
.
.

The original headers 'Account Details' and 'Account Name' are really just placeholders and are not needed. Any help would be appreciated.

Thanks.

  • A pivot table may help, or you'll need to use VBa to recreate the layout for you – Dave May 27 '15 at 12:09
  • So the product names, do they have a specific format? Or can they be anything from "cogs" to "whatchamajigs23"? What about account numbers? What about a pattern as to what the rows should be? – Raystafarian May 27 '15 at 12:19
  • @Raystafarian There are 127 different product names. They can probably be condensed to a lot fewer, but they vary wildly. The account numbers are mixed within the product names. I can seperate the account number list and the product name list, but am unable to create the link from account number to product. – sh7289 May 27 '15 at 12:29
  • So the pattern is always account number, then x products, then account number, x products - with the products belonging to the account? And in the next column each product has the correct corresponding value? – Raystafarian May 27 '15 at 12:31
  • @Raystafarian Yes, that is correct. Probably should have mentioned there are about 30,000 records. – sh7289 May 27 '15 at 13:20
0

Add 2 columns to your data, one to check for if the row is depicting account or product-level data (by checking if the first column is a number), and one that associates each product (and account) to the right account.

Raw data step

Then create a Pivot Table on the data, and configure your fields as such:

Pivot table step

If you want the account name instead of account #, adjust the formula in D4 to read off B4 instead of A4.

0

You can solve this problem by using regular expressions and converting to TSV.

If your data is not formatted exactly as your example, this will not work.

s/> account # (\d+) +(.+)\n> product 1 name +(.+)\n> product 2 name +(.+)\n> product 3 name +(.+)\n/account # \1\t\2\t\3\t\4\t\5\r/

Commented:

s/                       # We are replacing text
> account # (\d+)        # We are capturing the account number as \1
 +                       # Ignoring all spaces afterwords
(.+)\n                   # Capture the client name as \2 and look for a new line
> product 1 name +(.+)\n # Capture the product 1 value as \3
> product 2 name +(.+)\n # Capture the product 1 value as \4
> product 3 name +(.+)\n # Capture the product 1 value as \5
/                        # Done searching, start replacing
account # \1\t           # Write out the account number then a tab
\2\t\3\t\4\t\5           # Write out the client name and product values seperated by tabs
\r/                      # Write a new line for the next bit of data and terminate

Which will turn your data into:

account # 1    client name    product 1 value    product 2 value    product 3 value
account # 2    client name    product 1 value    product 2 value    product 3 value

Separated by tabs that can be easily imported to Excel.

Edit: It looks like you can use an application like Zeus Edit to find and replace this regular expression.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.