Is there some alternative solution which I have not considered? Is it possible to use the Transpose function? (which if so, I am more than happy to investigate myself) Or if I was able to get the fields into a similar column position, then the values would simply calculate a count of in the body of the table, rather than the value itself (as is the nature of PivotTables). So, my first point of call was to attempt to implement the above manipulation by inserting the data into a PivotTable, however I simply could not seem to figure the logic. It is columns 3 and 4 ( Field_Name and Value) which I would like to transpose into rows to produce something like the below: Request_ID Status Name Acc Number Date Enquiry Type ReasonĠ1000 Rejected John Smith 123456 Type 1 Reason 1Ġ1001 Completed Jane Jones 123457 Type 2 Reason 2Īs you can see, the second example above is much more logical and with less redundant data - and additionally will allow for simple filtering and analysis. The Field_Name column is the particular column repeats the same set of values on individual rows for each unique Request_ID - and the Value column lists the corresponding value relating to each Field_Name. The first two columns ( Request_ID and Status) are simply duplicated values to cater for the numerous rows of data relating to each individual request. The data in Excel is made up of approximately 700,000 rows currently, whereas once organised correctly should only consist of maybe 70,000, where the unique values of one particular column are transposed into some additional columns.Ĭurrently I am working with something like this (simplified) example below Request_ID Status Field_Name Value I am working with a data dump of transactions which is not exported from the particular system in a very friendly format for any purpose.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |