Hello -
I'm not even certain how to ask the question.
Everyday I export a file from Access into an .xls.
This .xls file looks the same as the Access file except:
1) I've inserted a new column "A" which concatenates 2 columns
2) I have added a column with some conditional statements

I would like to add a "note" column to this spreadsheet, but, everytime I
re-import the data from access, it does not keep the rows attached to the
"notes" column.

BEFO
NameA NameB Age Notes (not from Access, keyed in Excel)
ANDY SMITH 25 "Doesn't like tomatoes"
MATT JONES 32 "Likes Grapes"

AFTER:
MICKEY MOUSE 18 "Doesn't like tomatoes"
ANDY SMITH 25 "Likes Grapes"
MATT JONES 32

I want the "Doesn't like tomatoes" to follow ANDY, regardless of what row he
ends up on because of calculations made when importing. Matt should still say
"Likes Grapes" and Mickey should be empty because I just imported it and have
not added notes yet.

Thanks a whole bunch!

One thing you can do:

Use, say, Sheet1 for importing from Excel. Write your comments on the
right column.

Next time you import use Sheet2. Next to the data in Sheet2 enter the
formula (say in D2):

=INDEX(Sheet1:$D$2:$D$1000,MATCH(1,($A$2:$A$1000=A 2)*($B$2:$B$1000=B2)*($C$2:$C$1000=C2),0))

It is an array formula, thus use Shift+Ctrl+Enter to enter it. Copy it
along the column to import the comments.

Select all of Sheet2 data (Ctrl+* is an easy way). Copy and Paste
Special to Sheet1 using only Values.

Does this help?
Kostis Vezerides


DTTODGG wrote:
Hello -
I'm not even certain how to ask the question.
Everyday I export a file from Access into an .xls.
This .xls file looks the same as the Access file except:
1) I've inserted a new column "A" which concatenates 2 columns
2) I have added a column with some conditional statements

I would like to add a "note" column to this spreadsheet, but, everytime I
re-import the data from access, it does not keep the rows attached to the
"notes" column.

BEFO
NameA NameB Age Notes (not from Access, keyed in Excel)
ANDY SMITH 25 "Doesn't like tomatoes"
MATT JONES 32 "Likes Grapes"

AFTER:
MICKEY MOUSE 18 "Doesn't like tomatoes"
ANDY SMITH 25 "Likes Grapes"
MATT JONES 32

I want the "Doesn't like tomatoes" to follow ANDY, regardless of what row he
ends up on because of calculations made when importing. Matt should still say
"Likes Grapes" and Mickey should be empty because I just imported it and have
not added notes yet.

Thanks a whole bunch!

Thank you for your response, but I don't quite get it.
This is my formula, but I get #NAME? errors.
Do I need each column listed in the MATCH?

{=INDEX(Q_ContractDate_Q107!Sheet1,$J$2:$J$100,MAT CH(1,($A$2:$A$100=A5)*($B$2:$B$100=B5)*($C$2:$C$10 0=C5)*($D$2:$D$100=D5)*($E$2:$E$100=E5)*($F$2:$F$1 00=F5)*($G$2:$G$100=G5)*($H$2:$H$100=H5)*($I$2:$I$ 100=I5),0))}

What am I doing wrong?
Column "J" on Sheet 1 has my "Comments"
Column "J" on Sheet 2 has the formula

Thanks again!



"vezerid" wrote:

One thing you can do:

Use, say, Sheet1 for importing from Excel. Write your comments on the
right column.

Next time you import use Sheet2. Next to the data in Sheet2 enter the
formula (say in D2):

=INDEX(Sheet1:$D$2:$D$1000,MATCH(1,($A$2:$A$1000=A 2)*($B$2:$B$1000=B2)*($C$2:$C$1000=C2),0))

It is an array formula, thus use Shift+Ctrl+Enter to enter it. Copy it
along the column to import the comments.

Select all of Sheet2 data (Ctrl+* is an easy way). Copy and Paste
Special to Sheet1 using only Values.

Does this help?
Kostis Vezerides


DTTODGG wrote:
Hello -
I'm not even certain how to ask the question.
Everyday I export a file from Access into an .xls.
This .xls file looks the same as the Access file except:
1) I've inserted a new column "A" which concatenates 2 columns
2) I have added a column with some conditional statements

I would like to add a "note" column to this spreadsheet, but, everytime I
re-import the data from access, it does not keep the rows attached to the
"notes" column.

BEFO
NameA NameB Age Notes (not from Access, keyed in Excel)
ANDY SMITH 25 "Doesn't like tomatoes"
MATT JONES 32 "Likes Grapes"

AFTER:
MICKEY MOUSE 18 "Doesn't like tomatoes"
ANDY SMITH 25 "Likes Grapes"
MATT JONES 32

I want the "Doesn't like tomatoes" to follow ANDY, regardless of what row he
ends up on because of calculations made when importing. Matt should still say
"Likes Grapes" and Mickey should be empty because I just imported it and have
not added notes yet.

Thanks a whole bunch!

My apologies for a formula that I offered without thinking it too much.
I understand that your first sheet is Q_ContractDate_Q107. If so, in
the temporary sheet where you will paste the next month, use the
following formula:

=INDEX(Q_ContractDate_Q107!$J$2:$J$100,MATCH(1,(Q_ ContractDate_Q107!$A$2:$A$100=A5)*(Q_ContractDate_ Q107!$B$2:$B$100=B5)*(Q_ContractDate_Q107!$C$2:$C$ 100=C5)*(Q_ContractDate_Q107!$D$2:$D$100=D5)*(Q_Co ntractDate_Q107!$E$2:$E$100=E5)*(Q_ContractDate_Q1 07!$F$2:$F$100=F5)*(Q_ContractDate_Q107!$G$2:$G$10 0=G5)*(Q_ContractDate_Q107!$H$2:$H$100=H5)*($I$2:$ I$100=I5),0))

It should be array-entered (Ctrl+Shift+Enter)

HTH
Kostis

On Jan 24, 12:33 am, DTTODGG wrote:
Thank you for your response, but I don't quite get it.
This is my formula, but I get #NAME? errors.
Do I need each column listed in the MATCH?

{=INDEX(Q_ContractDate_Q107!Sheet1,$J$2:$J$100,MAT CH(1,($A$2:$A$100=A5)*($B$2:$B$100=B5)*($C$2:$C$10 0=C5)*($D$2:$D$100=D5)*($E$2:$E$100=E5)*($F$2:$F$1 00=F5)*($G$2:$G$100=G5)*($H$2:$H$100=H5)*($I$2:$I$ 100=I5),0))}

What am I doing wrong?
Column "J" on Sheet 1 has my "Comments"
Column "J" on Sheet 2 has the formula

Thanks again!

"vezerid" wrote:
One thing you can do:

Use, say, Sheet1 for importing from Excel. Write your comments on the
right column.

Next time you import use Sheet2. Next to the data in Sheet2 enter the
formula (say in D2):

=INDEX(Sheet1:$D$2:$D$1000,MATCH(1,($A$2:$A$1000=A 2)*($B$2:$B$1000=B2)*($C$2:$C$1000=C2),0))

It is an array formula, thus use Shift+Ctrl+Enter to enter it. Copy it
along the column to import the comments.

Select all of Sheet2 data (Ctrl+* is an easy way). Copy and Paste
Special to Sheet1 using only Values.

Does this help?
Kostis Vezerides

DTTODGG wrote:
Hello -
I'm not even certain how to ask the question.
Everyday I export a file from Access into an .xls.
This .xls file looks the same as the Access file except:
1) I've inserted a new column "A" which concatenates 2 columns
2) I have added a column with some conditional statements

I would like to add a "note" column to this spreadsheet, but, everytime I
re-import the data from access, it does not keep the rows attached to the
"notes" column.

BEFO
NameA NameB Age Notes (not from Access, keyed in Excel)
ANDY SMITH 25 "Doesn't like tomatoes"
MATT JONES 32 "Likes Grapes"

AFTER:
MICKEY MOUSE 18 "Doesn't like tomatoes"
ANDY SMITH 25 "Likes Grapes"
MATT JONES 32

I want the "Doesn't like tomatoes" to follow ANDY, regardless of what row he
ends up on because of calculations made when importing. Matt should still say
"Likes Grapes" and Mickey should be empty because I just imported it and have
not added notes yet.

Thanks a whole bunch!

Kostis,

Thank you so much! This works very well.

Question:
If I have a unique column, do I need to MATCH on every column? Seems like a
great possibility for typo errors.

{=INDEX(Sheet1!$J$2:$J$100,MATCH(1,(Sheet1!$A$2:$A $100=A2),0))}

I don't really understand the INDEX MATCH function

If I have a unique column, is VLOOKUP a better application?

Thank you!


"vezerid" wrote:

My apologies for a formula that I offered without thinking it too much.
I understand that your first sheet is Q_ContractDate_Q107. If so, in
the temporary sheet where you will paste the next month, use the
following formula:

=INDEX(Q_ContractDate_Q107!$J$2:$J$100,MATCH(1,(Q_ ContractDate_Q107!$A$2:$A$100=A5)*(Q_ContractDate_ Q107!$B$2:$B$100=B5)*(Q_ContractDate_Q107!$C$2:$C$ 100=C5)*(Q_ContractDate_Q107!$D$2:$D$100=D5)*(Q_Co ntractDate_Q107!$E$2:$E$100=E5)*(Q_ContractDate_Q1 07!$F$2:$F$100=F5)*(Q_ContractDate_Q107!$G$2:$G$10 0=G5)*(Q_ContractDate_Q107!$H$2:$H$100=H5)*($I$2:$ I$100=I5),0))

It should be array-entered (Ctrl+Shift+Enter)

HTH
Kostis

On Jan 24, 12:33 am, DTTODGG wrote:
Thank you for your response, but I don't quite get it.
This is my formula, but I get #NAME? errors.
Do I need each column listed in the MATCH?

{=INDEX(Q_ContractDate_Q107!Sheet1,$J$2:$J$100,MAT CH(1,($A$2:$A$100=A5)*($B$2:$B$100=B5)*($C$2:$C$10 0=C5)*($D$2:$D$100=D5)*($E$2:$E$100=E5)*($F$2:$F$1 00=F5)*($G$2:$G$100=G5)*($H$2:$H$100=H5)*($I$2:$I$ 100=I5),0))}

What am I doing wrong?
Column "J" on Sheet 1 has my "Comments"
Column "J" on Sheet 2 has the formula

Thanks again!

"vezerid" wrote:
One thing you can do:

Use, say, Sheet1 for importing from Excel. Write your comments on the
right column.

Next time you import use Sheet2. Next to the data in Sheet2 enter the
formula (say in D2):

=INDEX(Sheet1:$D$2:$D$1000,MATCH(1,($A$2:$A$1000=A 2)*($B$2:$B$1000=B2)*($C$2:$C$1000=C2),0))

It is an array formula, thus use Shift+Ctrl+Enter to enter it. Copy it
along the column to import the comments.

Select all of Sheet2 data (Ctrl+* is an easy way). Copy and Paste
Special to Sheet1 using only Values.

Does this help?
Kostis Vezerides

DTTODGG wrote:
Hello -
I'm not even certain how to ask the question.
Everyday I export a file from Access into an .xls.
This .xls file looks the same as the Access file except:
1) I've inserted a new column "A" which concatenates 2 columns
2) I have added a column with some conditional statements

I would like to add a "note" column to this spreadsheet, but, everytime I
re-import the data from access, it does not keep the rows attached to the
"notes" column.

BEFO
NameA NameB Age Notes (not from Access, keyed in Excel)
ANDY SMITH 25 "Doesn't like tomatoes"
MATT JONES 32 "Likes Grapes"

AFTER:
MICKEY MOUSE 18 "Doesn't like tomatoes"
ANDY SMITH 25 "Likes Grapes"
MATT JONES 32

I want the "Doesn't like tomatoes" to follow ANDY, regardless of what row he
ends up on because of calculations made when importing. Matt should still say
"Likes Grapes" and Mickey should be empty because I just imported it and have
not added notes yet.

Thanks a whole bunch!

Yes, this is correct. If you have a unique column (a "key" as it is
called in database lingo) then VLOOKUP on the key will do the job. If
your key is in A:A then

=VLOOKUP(A2,Sheet1!A:J,10,FALSE)

HTH
Kostis

On Jan 24, 4:39 pm, DTTODGG wrote:
Kostis,

Thank you so much! This works very well.

Question:
If I have a unique column, do I need to MATCH on every column? Seems like a
great possibility for typo errors.

{=INDEX(Sheet1!$J$2:$J$100,MATCH(1,(Sheet1!$A$2:$A $100=A2),0))}

I don't really understand the INDEX MATCH function

If I have a unique column, is VLOOKUP a better application?

Thank you!

"vezerid" wrote:
My apologies for a formula that I offered without thinking it too much.
I understand that your first sheet is Q_ContractDate_Q107. If so, in
the temporary sheet where you will paste the next month, use the
following formula:

=INDEX(Q_ContractDate_Q107!$J$2:$J$100,MATCH(1,(Q_ ContractDate_Q107!$A$2:$A$100=A5)*(Q_ContractDate_ Q107!$B$2:$B$100=B5)*(Q_ContractDate_Q107!$C$2:$C$ 100=C5)*(Q_ContractDate_Q107!$D$2:$D$100=D5)*(Q_Co ntractDate_Q107!$E$2:$E$100=E5)*(Q_ContractDate_Q1 07!$F$2:$F$100=F5)*(Q_ContractDate_Q107!$G$2:$G$10 0=G5)*(Q_ContractDate_Q107!$H$2:$H$100=H5)*($I$2:$ I$100=I5),0))

It should be array-entered (Ctrl+Shift+Enter)

HTH
Kostis

On Jan 24, 12:33 am, DTTODGG wrote:
Thank you for your response, but I don't quite get it.
This is my formula, but I get #NAME? errors.
Do I need each column listed in the MATCH?

{=INDEX(Q_ContractDate_Q107!Sheet1,$J$2:$J$100,MAT CH(1,($A$2:$A$100=A5)*($B$2:$B$100=B5)*($C$2:$C$10 0=C5)*($D$2:$D$100=D5)*($E$2:$E$100=E5)*($F$2:$F$1 00=F5)*($G$2:$G$100=G5)*($H$2:$H$100=H5)*($I$2:$I$ 100=I5),0))}

What am I doing wrong?
Column "J" on Sheet 1 has my "Comments"
Column "J" on Sheet 2 has the formula

Thanks again!

"vezerid" wrote:
One thing you can do:

Use, say, Sheet1 for importing from Excel. Write your comments on the
right column.

Next time you import use Sheet2. Next to the data in Sheet2 enter the
formula (say in D2):

=INDEX(Sheet1:$D$2:$D$1000,MATCH(1,($A$2:$A$1000=A 2)*($B$2:$B$1000=B2)*($C$2:$C$1000=C2),0))

It is an array formula, thus use Shift+Ctrl+Enter to enter it. Copy it
along the column to import the comments.

Select all of Sheet2 data (Ctrl+* is an easy way). Copy and Paste
Special to Sheet1 using only Values.

Does this help?
Kostis Vezerides

DTTODGG wrote:
Hello -
I'm not even certain how to ask the question.
Everyday I export a file from Access into an .xls.
This .xls file looks the same as the Access file except:
1) I've inserted a new column "A" which concatenates 2 columns
2) I have added a column with some conditional statements

I would like to add a "note" column to this spreadsheet, but, everytime I
re-import the data from access, it does not keep the rows attached to the
"notes" column.

BEFO
NameA NameB Age Notes (not from Access, keyed in Excel)
ANDY SMITH 25 "Doesn't like tomatoes"
MATT JONES 32 "Likes Grapes"

AFTER:
MICKEY MOUSE 18 "Doesn't like tomatoes"
ANDY SMITH 25 "Likes Grapes"
MATT JONES 32

I want the "Doesn't like tomatoes" to follow ANDY, regardless of what row he
ends up on because of calculations made when importing. Matt should still say
"Likes Grapes" and Mickey should be empty because I just imported it and have
not added notes yet.

Thanks a whole bunch!