Transposing data SETS from vertical to horizontal

[tutuzdad]

I desperately need to transpose multiple SETS of data so that the now vertical columns are horizontal. I know about transposing but not when it comes to sets. For simplicity sake let’s say I have a long list of multiple addresses with the headers, NAME, ADDR, CITY, STATE, ZIP. Transposing these headers into a horizontal table is fairly simple. It’s getting the 5 rows of names, addresses, cities, states and zips under the correct headers that has me confounded. I’m using 2010. I’m pretty familiar with excel. Ideally a solution that I can reapply to future lists since this is something I do regularly and my output is always formatted this same annoying way. I don’t do VB but if we have to and you can simply it step-by-step for me I’d really appreciate it.

 

[hiker95]

tutuzdad,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, flat text) try one of the following:

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

 

[tutuzdad]

tutuzdad, Welcome to the MrExcel forum. 1. What version of Excel and Windows are you using? 2. Are you using a PC or a Mac? To start off, and, so that we can get it right on the first try: Can you post a screenshot of the actual raw data worksheet? And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for? To post a small screen shot (NOT a graphic, or, picture, or, flat text) try one of the following: MrExcel HTML Maker If you do not know how to install and how to use HTML Mr.Excel Maker https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be Excel Jeanie Download Borders-Copy-Paste http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045 To test the above: Test Here Or, you can upload your workbook to Box Net, sensitive data changed mark the workbook for sharing and provide us with a link to your workbook.

Thank you for your quick reply to my time sensitive question. I am using a Windows 7 PC with Excel 2010. Unfortunately I cannot post the original data as it contains confidential information and I cannot install programs as I work in a sensitive field where user installations are Big Brother controlled. In its simplest form however, the data is as I described, very similar to an address list with 5 headers in column A and the unique sets of data in Column B. I will try to provide a text sample here using a fictitious address list (hoping that things line up well enough to make sense): ________(A)________(B)_______ (1)----Name-------Fred (2)----Addr-------101 Main St. (3)----City-------Anchorage (4)----State------AK (5)----Zip--------99501 (6)----Name-------Carl (7)----Addr-------21 First St. (8)----City-------Clifford (9)----State------ND (10)---Zip--------58016 (11)---Name-------Norman (12)---Addr-------200 Paved St. (13)---City-------Austin (14)---State------TX (15)---Zip--------73301 These 5 headers in column "A" above repeat down the page to around A:3000 or so (this varies per query though - suffice it to say that there are a LOT of them each time). If I were to transpose the 5 headers in column "A" and put them on the same row so that they became table headers of A1, B1, C1, D1 & E1, the trick I need is something that will make each complete address set in column "B" above to also end up on its own row and each element of the address under its appropriate header, as shown below (where B1, B2, B3, B4 & B5 shown above transpose to A2, B2, C2, D2, & E2, shown below, B6, B7, B8, B9 & B10 shown above transpose to A3, B3, C3, D3 & E3, shown below, etc. throughout the entire list): ________(A)________(B)____________(C)____________(D)_______(E) (1)----Name---------ADDR-----------------City---------------State-------ZIP---- (2)----Fred----------101 Main St.---------Anchorage--------AK----------99501-- (3)----Carl-----------21 First St.----------Clifford------------ND----------58016-- (4)----Norman------200 Paved St.--------Austin-------------TX----------73301--

 

[tutuzdad]

Well that clearly didn't work. I am using a Windows 7 PC with Excel 2010. Unfortunately I cannot post the original data as it contains sensitive information and I cannot install programs as I work in a sensitive field where installations are firewall controlled. In its simplest form however, the data is as I described, very similar to an address list with 5 headers in column A and the unique data in Column B. I will try to elaborate: Let’s say the 5 headers in column "A" repeat down the page to around A:3000 or so (this varies per query though - suffice it to say that there are a LOT of them each time). NAME ADDR CITY STATE ZIP The trick I need is something that will transpose the now vertical 5 headers in column A on rows A1, A2, A3, A4 & A5 so they end up horizontal table headers like this: NAME---ADDR---CITY--STATE—ZIP (in A1, B1, C1, D1 & E1) “AND THEN”…. …transpose each complete address set originally listed vertically in column "B" to its own horizontal row ensuring that each element of the addresses ends up under the correct header (in other words, where column B’s B1, B2, B3, B4 & B5 transposes to row 2’s A2, B2, C2, D2, & E2, and column B’s B6, B7, B8, B9 & B10 transposes to row 3’s A3, B3, C3, D3 & E3, and so forth throughout the entire list). Man, I hope that makes sense.

 

[hiker95]

tutuzdad,

I can not work with, nor will I re-format your last two text replies.

Unfortunately I cannot post the original data as it contains sensitive information and I cannot install programs as I work in a sensitive field where installations are firewall controlled.

You can try the following (nothing to install) with sensitive data changed:

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


To test the above:
Test Here


If you are not able provide the above, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[tutuzdad]

tutuzdad, I can not work with, nor will I re-format your last two text replies. You can try the following (nothing to install) with sensitive data changed: Borders-Copy-Paste http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045 To test the above: Test Here If you are not able provide the above, then: Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

My reply is straightforward and simple...it's an "address list". I don't know what's not to be understood about that. Perhaps someone else who can visualize what I am describing and who also understands that convenience is not always possible (even in the best of circumstances) will stop by and help me with my problem. I hope it happens soon.

 

[tutuzdad]

my reply is straightforward and simple...it's an "address list". I don't know what's not to be understood about that. Perhaps someone else who can visualize what i am describing and who also understands that convenience is not always possible (even in the best of circumstances) will stop by and help me with my problem. I hope it happens soon.

bump

 

[tutuzdad]

Perhaps I can re-word this so that it doesn't sound so complicated. I am using a Windows 7 PC with Excel 2010. I have a list of Name/Address sets that consist of (A1) NAME (A2) ADDR (A3) CITY (A4) STATE (A5) ZIP. Without any spaces in between, this list repeats with a new address set beginning in (A6) and so forth through about (A:3000, or roughly 600 names and addresses). I want to create a new table for this information ON 'SHEET 2'. The table headers will be: (A1) NAME (B1) ADDR (C1) CITY (D1) STATE (E1) ZIP. What I need is some method to move the vertical address sets on ‘sheet 1’ to the table on ‘sheet 2’, ensuring that each bit lands in the correct column and the end result is each address is on a single row. Since I have these lists regularly, the solution would ideally be one that I can implement in the future by simply replacing the address list on ‘sheet 1’ with a new similarly formatted address list. If anyone can help I’d sure appreciate it. My needs are time sensitive and this forum came highly recommended. Thank you in advance for your time.

 

[Lee.]

Are you wanting to convert this?

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K
1	first_name	last_name	company_name	address	city	county	postal	phone1	phone2	email	web
2	Aleshia	Tomkiewicz	Alan D Rosenburg Cpa Pc	14 Taylor St	St. Stephens Ward	Kent	CT2 7PP	01835-703597	01944-369967	atomkiewicz@hotmail.com	http://www.alandrosenburgcpapc.co.uk
3	Evan	Zigomalas	Cap Gemini America	5 Binney St	Abbey Ward	Buckinghamshire	HP11 2AX	01937-864715	01714-737668	evan.zigomalas@gmail.com	http://www.capgeminiamerica.co.uk
4	France	Andrade	Elliott, John W Esq	8 Moor Place	East Southbourne and Tuckton W	Bournemouth	BH6 3BE	01347-368222	01935-821636	france.andrade@hotmail.com	http://www.elliottjohnwesq.co.uk
5	Ulysses	Mcwalters	Mcmahan, Ben L	505 Exeter Rd	Hawerby cum Beesby	Lincolnshire	DN36 5RP	01912-771311	01302-601380	ulysses@hotmail.com	http://www.mcmahanbenl.co.uk
uk-500

Into this

Excel 2012
	A	B	C	D	E
1	first_name	Aleshia	Evan	France	Ulysses
2	last_name	Tomkiewicz	Zigomalas	Andrade	Mcwalters
3	company_name	Alan D Rosenburg Cpa Pc	Cap Gemini America	Elliott, John W Esq	Mcmahan, Ben L
4	address	14 Taylor St	5 Binney St	8 Moor Place	505 Exeter Rd
5	city	St. Stephens Ward	Abbey Ward	East Southbourne and Tuckton W	Hawerby cum Beesby
6	county	Kent	Buckinghamshire	Bournemouth	Lincolnshire
7	postal	CT2 7PP	HP11 2AX	BH6 3BE	DN36 5RP
8	phone1	01835-703597	01937-864715	01347-368222	01912-771311
9	phone2	01944-369967	01714-737668	01935-821636	01302-601380
10	email	atomkiewicz@hotmail.com	evan.zigomalas@gmail.com	france.andrade@hotmail.com	ulysses@hotmail.com
11	web	http://www.alandrosenburgcpapc.co.uk	http://www.capgeminiamerica.co.uk	http://www.elliottjohnwesq.co.uk	http://www.mcmahanbenl.co.uk
Sheet1

Please note: these are sample/dummy datasets

 

[tutuzdad]

Thank you for your reply. What I am needing is exactly the opposite, but you definitely have the right idea. If you can show me how to transpose your 11 row address set (which presumably runs off to infinity like mine) to your 5 row table example the mystery will be solved.