Moving cell data from columns to rows in massive file

Hello All,

I have a semi large excel file with close to five thousand rows and aprx.sixteen columns. In column "A" is a listing of all my customers (a lot with the same name, but different addresses). When this file was created each row had a certain type of equipment, so one individual customer could have eight rows. You can imagine my frustration when I have to redesign this file and have one row that has their name in one column, address in another, etc. along with each type of equipment in it's own predetermined column.

Besides filtering each individual customer (aprx 2k different) and physically move the cells to the correct column, is there an easier way.

Please see the attached file for a quick visual.

Excel Example.jpg

Thank you all in advance,
Aaron

The purpose of this procedure will be to import the .csv file into SQL for easier queries

This can be done with formulae.

Post the workbook rather than a picture of it!

Originally Posted by Marcol

This can be done with formulae.

Post the workbook rather than a picture of it!

The customer and equipment list is very critical information due to financial institutions it contains.

May I create a new workbook and use that instead?

Originally Posted by Marcol

This can be done with formulae.

Post the workbook rather than a picture of it!

Here is a workbook that I created with three sheets.
The readme shows what I am facing.
Sheet two is the orginal file I created and the last sheet is what I am in need of.

I would love to know this because I am sent customer and equipment listing every quarter.

Thank you all!

Aaron

TEST WORKBOOK.xls

Can anyone please help me with this issue or maybe suggest some links?

hi aaroninbna, can you explain logic of allocating equipment to the final columns. How to decide which column should take the quantity and equipment?

Originally Posted by watersev

hi aaroninbna, can you explain logic of allocating equipment to the final columns. How to decide which column should take the quantity and equipment?

The idea was for my technicians to be able to do a quick search on a specific customer, their equipment type with quantities via a search feature in Joomla (web development) site. Currently they have to filter down to the customer when generating a service call, by creating a jquery, the results are almost instantanious. Joomla requires SQL and the "Get"/"Post" functions look for a specific row and the columns in that row (in SQL).

As the excel worksheet (the original) will have customer "A" using five to six rows and two columns. This would be a logistical nightmare to drag/drop every row (6-8k) for the final product, which would be imported into SQL.

I attempted a few hundred, but stopped after my uncontrollable desire to gulp massive quantities of adult beverages took over. Instead, I closed my workbook and made another cup of coffee.

this explains the purpose, what about the logic if you do still need it?

Oh yes, please.

If there is an easier process, I am more than willing to learn and implement.

This forum is a great resource, because walking around the office, everyone has the "deer in the headlights" look. I think they still have problems with autosum.

Thank you again,
Aaron

in order to help you need to provide algorithm of getting end result as you do it manually, what, where and why.

Hey Water,

Currently the only way my technicians look up a specific customer number and equipment, is to use the filter option or a CTL+F. I want to be able "somewhat easy" work-around to move every data from (see the sheet labeled "Original File" and "Final File") a vertical layoput to a horizontal layout.

After all of my data is altered, I will save it as a .csv file and export it into a SQL database in our company website and the technicians can use a modified search feature (Joomla V2.5) to get/post the data for easy service to our customers. I have other extensions that will also post a Google Map and other data.

My only problem is to the extremely large file that I have to convert.

Take Care,
Aaron

hi Aaron, nice to get you back, a couple of questions:

data block:

112233445566 BELKINS LLC 480 N BROADWAY STE SACRAMENTO DMP 123 2
112233445566 BELKINS LLC 480 N BROADWAY STE SACRAMENTO ULTRAK 123 23
112233445566 BELKINS LLC 480 N BROADWAY STE SACRAMENTO HONEYWELL 123 32
112233445566 BELKINS LLC 480 N BROADWAY STE SACRAMENTO BOSCH 123 16
112233445566 BELKINS LLC 480 N BROADWAY STE SACRAMENTO ULTRAK 234 2

So we are taking unique customer No and address and move data from rows to columns.

Questions:

- why does DMP 123 goes to Equip_A? Why does Equip_B is empty?
- can you give reference table of what equipment would be taken to which group?
- do we have all those Equip_X columns in advance or calculating them from original data?

Originally Posted by watersev

Questions:

- why does DMP 123 goes to Equip_A? Why does Equip_B is empty?
- can you give reference table of what equipment would be taken to which group?
- do we have all those Equip_X columns in advance or calculating them from original data?

That you so much for the special interest you have taken with my issue!

Q1) - why does DMP 123 goes to Equip_A? Why does Equip_B is empty?
A) Not all customers will have the same equipment item. This will be used for a jquery data search (quanity per area/region,etc) When a certain type of equipment is not present at the customer's address, the field will ne 'nulled" therefore not being tallied in the query.

Q2) - can you give reference table of what equipment would be taken to which group?
A) That is really difficult because certain customer have constantly changing products.

Would it be easier to have all equipment populate the next available row's column for equipment? I receive the worksheet every month and believe that creating a workable formula could be beyond one tolerance for patience.

Q3) - do we have all those Equip_X columns in advance or calculating them from original data?
A) Yes we do, but I am afraid that the constant worksheet changes might create a headache.

Again, thank you and am excited to hear back from you.

Aaron

please check attachment, run code "test", hope this helps