1

Looking to rearrange data so every instance of EX is merged into one row with accompanying info to more easily read data.

Image Example

Example Desired Output

Attie
  • 19,974
ToppleWave
  • 11
  • 2
  • 1
    Transposing each group individually seems to be the only option but would take days with how much data there is – ToppleWave Mar 11 '20 at 16:45
  • 1
    Can you show us what you'd like the final product to look like? Also, is the 'EXn' in the VendorNumber field the same string as what's preceding the Name/Address/etc data? Also, can we assume the 'EX3' in J17 is a typo? – Alex M Mar 11 '20 at 16:55
  • 1
    Yes that is a typo. I added a desired output example so hope that helps! Data is exported so the format is very tough to work with and rearrange. – ToppleWave Mar 11 '20 at 17:03
  • 1
    My 2nd question again. Say VendorNumber for EX3 is 0110110, does J10 contain 0110110 John Smith? Or just John Smith? – Alex M Mar 11 '20 at 17:22
  • 1
    Just "John Smith". I used EXn to denote relationship, sorry if that was confusing – ToppleWave Mar 11 '20 at 17:31
  • This looks like it was imported from a file, is that accurate? I ask as this might be easier to solve while importing rather than after it's imported – Ack Mar 11 '20 at 17:53
  • 1
    some possibly helpful related content at https://superuser.com/questions/709091/how-to-combine-values-from-multiple-rows-into-a-single-row-using-module and https://superuser.com/questions/1198140/excel-data-in-rows-and-columns-to-be-reformatted – Alex M Mar 11 '20 at 18:21
  • 1
    Yes it was imported. Unfortunately I do not have access to the original file and only have the data given to me. Because of the way the data is crammed in one column it makes transposing impossible and since some fields are left blank (e.g. contact is left blank in 80%) I cannot assign numbers to the rows and filter that way without going through every line. – ToppleWave Mar 11 '20 at 18:22
  • Because you don't have access to the original file, I've provided an alternate way in my answer – Ack Mar 11 '20 at 18:50

1 Answers1

3

Your data is separated by a blank row, this is good and we are going to use that.

Export the data to .txt file.

  1. save as
  2. file type = .txt

Use Word to strip all unneeded separation information:

Remove extra tabs

  1. open search and replace ctrl+H
  2. Find what = ^t^t
  3. Replace with = ^t
  4. replace all
  5. repeat until no more are found

Reduce to single rows and remove the blank row, using a temp holding value

  1. open search and replace ctrl+H
  2. Find what = ^p^p
  3. Replace with = XX
  4. replace all
  5. Find what = ^p
  6. Replace with ^t
  7. replace all
  8. Find what = XX
  9. Replace with ^p
  10. replace all

Import back into Excel

  1. Copy all (Ctrl+A, Ctrl+C)
  2. Paste into Excel (Ctrl+V)
spikey_richie
  • 8,951
Ack
  • 633