Converting Grid to Flat File

smoskowitz

New Member
Joined
Jul 21, 2010
Messages
10
Hello --

I am a VBA newbie trying to make my job easier.

I currently have a file in the following format

AAA BBB CCC DDD EEE FFF GGG​
1
2
3
4
5
6
7

How would I writer code to create an excel sheet that looks more like this:

AAA 1
AAA 2
AAA 3
.
.
.
GGG 7

All I want to do is convert the grid format to more of a flat file format and don't know how to do this. I would like the code to be dynamic enough to figure out how many rows and columns I have.

Thanks,
Seth
 
Last edited:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
hi, and welcome to the board.

where does it start and end? which ranges? like, it goes from A1 thru J10, or what?
 
Upvote 0
The Alpha codes(going down) start in A2:A119 and the numeric codes go from B1:k1.

Hopefully, I answered your question.

Seth
 
Upvote 0
ok good, and one more quick Q, sorry - you want the new data set where? which column, starting where?
 
Upvote 0
so you have a table with 118 rows (not counting headers) and 10 columns of data.
in A1 of Sheet2, type this:
=INDEX(Sheet1!$B$2:$K$118,INT(ROW()/118+1),MOD(ROW()-1,10)+1)

and copy down 1180 rows. actually doesnt matter which column you put this in, but has to start at Row 1. like A1, B1, etc.

P.S. for a "header column", you can use this:

=INDEX(Sheet1!$A$2:$A$119,INT(ROW()/119+1)) & " " & INDEX(Sheet1!$B$1:$K$1,MOD(ROW()-1,10)+1)

again, starts in Row #1, any column

if you do it right, this is what you get:

4815771093_d882aee1b3_b.jpg
 
Last edited:
Upvote 0
Thanks but I don't think that represented what I wanted to do. Below is my actual data. The row starts in A2 and the columns start in B1. What I want to do is turn what is listed below into a two column file. Thank you for the effort you are putting into this.

So in essence it is:

Column A Column B
IPG.............P-20
MGI.............P-20
AMC............P-20

PCI..............T-82
etc

P-20 P-21A P-22 P-33 P-37 T-29A T-51A T-80T-81 T-82​
IPG
MGI
AMC
AGA
T-BGW
BNC
T-BGI
T-HMPI
CEI
PRC
CAM
CEA
CLI
CPP
RXM
CAU
CTB
T-BSMG
MPE
T-CPS
DAI
DLA
DEU
DPR
DAM
DE
T-FCBI
CTI
EML
T-FCBJ
AKA
GAM
RMI
ORB
HHC
HUG
HMS
IDM
INEX
IPGDEINEX
ICC
IPGDEICC
IHCI
IPGDEIHCI
SNA
ISV
IGU
IAI
PIC
JMC
JAI
KSE
LCF
APL
BCI
LGH
MGU
MAC
MCT
MSA
MRM
MWM
MEM
MUS
MWW
T-MDW
MBE
MFI
MPC
WIM
MIC
MGC
MAI
MVP
KAL
NAS
NSA
AII
OCT
OMA
ISO
OAI
IPGDEPCI
PMK
PSU
PEI
RCW
REP
T-RGMI
ZCI
KBA
T-MDSF
RBI
SCI
SGI
IPGDESPL
FBI
GGI
T-THGI
TIP
TMA
MWA
TSG
T-TPI
T-TMLP
T-TMT
T-TMH
TLH
TLC
T-TNC
T-TAP
T-TNH
T-TLA
CEC
ARG
WTI
IPGDEEXP
PCI
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top