Autofill help accessing random cells on separate sheet reference

[nemish]

I'm looking to take a exported database that outputs in the format

A5 Customer Number
B5 Name
A6 Address1
A7 Address2
H6 Home #
H7 Work #
L6 First Date
L7 Last Date
H8 E-Mail
L8 Birthday

and repeats
A9 Customer Number
...

I want to reformat this on Sheet2
A1 Customer Number
B1 Name
C1 Address1
D1 Address2
E1 Home#
F1 Work#
G1 Email
H1 Birthday
I1 First Date
J1 Last Date

and repeat
A2 Customer Number

Since the formatting is all over the place I haven't been able to figure out a formula on Sheet 2 that will allow me to autofill and grab the customer information.
I've tried INDIRECT and INDEX with no luck.
I'm sure this has to be possible

Thanks

 

[jasonb75]

Welcome to the board!

A vba solution would probably be more practical if you can use it. With a lot of data using a formula could result in some slow calculaiton times.

In A1 of Sheet2 enter, then autofilll to J1 and down as needed.

=OFFSET(Sheet1!$A$5,((ROW()-1)*4)+CHOOSE(COLUMN(),0,0,1,2,1,2,3,3,1,2),CHOOSE(COLUMN(),0,1,0,0,7,7,7,11,11,11))

 

[nemish]

Welcome to the board!

A vba solution would probably be more practical if you can use it. With a lot of data using a formula could result in some slow calculaiton times.

In A1 of Sheet2 enter, then autofilll to J1 and down as needed.

=OFFSET(Sheet1!$A$5,((ROW()-1)*4)+CHOOSE(COLUMN(),0,0,1,2,1,2,3,3,1,2),CHOOSE(COLUMN(),0,1,0,0,7,7,7,11,11,11))

I don't know enough about VBA but I can probably use it.

As far as the formula above... it actually works for all except for F and J on the resulting Sheet2... I can't understand exactly what it's doing in order to modify it to fix those 2 cells


too bad I can't attach a sample workbook here

 

[jasonb75]

How are the results differing from the expected with F and J?

The formula is set up to match exactly as you listed the details in your original post, so if you simplified the layout for posting then the formula will need changing to allow for that.

This table shows the cells in sheet 1 that the data will be pulled from as you fill the formula, note that if you start the formula in B2 instead of A1 then the result will not be the same.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 48px"><COL style="WIDTH: 47px"><COL style="WIDTH: 48px"><COL style="WIDTH: 48px"><COL style="WIDTH: 47px"><COL style="WIDTH: 47px"><COL style="WIDTH: 48px"><COL style="WIDTH: 48px"><COL style="WIDTH: 44px"><COL style="WIDTH: 45px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>$A$5</TD><TD>$B$5</TD><TD>$A$6</TD><TD>$A$7</TD><TD>$H$6</TD><TD>$H$7</TD><TD>$H$8</TD><TD>$L$8</TD><TD>$L$6</TD><TD>$L$7</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>$A$9</TD><TD>$B$9</TD><TD>$A$10</TD><TD>$A$11</TD><TD>$H$10</TD><TD>$H$11</TD><TD>$H$12</TD><TD>$L$12</TD><TD>$L$10</TD><TD>$L$11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>$A$13</TD><TD>$B$13</TD><TD>$A$14</TD><TD>$A$15</TD><TD>$H$14</TD><TD>$H$15</TD><TD>$H$16</TD><TD>$L$16</TD><TD>$L$14</TD><TD>$L$15</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

 

[nemish]

How are the results differing from the expected with F and J?

The formula is set up to match exactly as you listed the details in your original post, so if you simplified the layout for posting then the formula will need changing to allow for that.

On Sheet2 E is actually showing Work# rather than Home# so Home# and Work# column is actually displaying wrong results.
On Sheet2 I is actually showing Last Date rather than First Date so First Date and Last Date column are actually displaying wrong results as well.

It definitely could be something I typo'd... I'm trying to confirm that right now

...

Yep that's exactly what happened... I typo'd

H6 Home #
H7 Work #
L6 First Date
L7 Last Date

Should be

H5 Home #
H6 Work #
L5 First Date
L6 Last Date

 

[nemish]

as I said If i understood what this formula was doing I could modify it... sorry for being so naive.

 

[jasonb75]

Easily done, try

=OFFSET($A$5,((ROW()-1)*4)+CHOOSE(COLUMN(),0,0,1,2,0,1,3,3,0,1),CHOOSE(COLUMN(),0,1,0,0,7,7,7,11,11,11))

 

[nemish]

Figured it out:

=OFFSET(Sheet1!$A$5,((ROW()-1)*4)+CHOOSE(COLUMN(),0,0,1,2,0,1,3,3,0,1),CHOOSE(COLUMN(),0,1,0,0,7,7,7,11,11,11))

SO 0 is row 5, 1 is row 6, 2 is row 7 and 3 is row 8...

just not sure I understand the second half:

0,1,0,0,7,7,7,11,11,11

 

[nemish]

Easily done, try

=OFFSET($A$5,((ROW()-1)*4)+CHOOSE(COLUMN(),0,0,1,2,0,1,3,3,0,1),CHOOSE(COLUMN(),0,1,0,0,7,7,7,11,11,11))

Yep that was it...

OK now to make it more complicated... is there a way to not display 0's in columns where there were blanks?

 

[jasonb75]

Figured it out:

=OFFSET(Sheet1!$A$5,((ROW()-1)*4)+CHOOSE(COLUMN(),0,0,1,2,0,1,3,3,0,1),CHOOSE(COLUMN(),0,1,0,0,7,7,7,11,11,11))

SO 0 is row 5, 1 is row 6, 2 is row 7 and 3 is row 8...

just not sure I understand the second half:

0,1,0,0,7,7,7,11,11,11

0 is row 5, 1 is row 6, 2 is row 7 and 3 is row 8 when the formula is in row 1, when it's in row 2 then 0 is row 9, 1 is row 10, 2 is row 11 and 3 is row 12, and so on.

second half is number of columns to the right of column A

I was trying to figure out a way to explain it, made it a bit easier when you figured part out yourself

This may or may not work depending on the format of your data.

=T(OFFSET(Sheet1!$A$5,((ROW()-1)*4)+CHOOSE(COLUMN(),0,0,1,2,0,1,3,3,0,1),CHOOSE(COLUMN(),0,1,0,0,7,7,7,11,11,11)))

If all data is in text format it will work, if dates or phone # are in numeric formay then it will blank those as well. There are alternatives if needed, but that was the quick and simple version