Excel macro merge cells 1

[andycapp28]

I have an excel spreadsheet that I need advice on how to merge rows/cols and drop the surplus rows. The following sample from the excel file is as follows.
Row Col A Col B Col C Col D Col E Col F
1 24/03/92-139 5668 Kilkeel 81161 Wool £1,157.38
2 24/03/92-139 5668 Kilkeel 3901 Cotton £1,157.38
3 24/03/92-139 5668 Kilkeel 1079 Paper £1,157.38

I need on change of Col B to concatanate Col D and Col E from rows 1, 2 and 3 into row 1 Col D and Col E. Then drop rows 2 and 3. The results I require would hopefully look something like this.

Row Col A Col B Col C Col D
1 24/03/92-139 5668 Kilkeel 81161 and 3901 and 1079
Col E Col F
Wool and Cotton and Paper £1,157.38

Is this possible to achieve, if so I need help and must include contingency for Col B value appearing just the once.

I would really appreciate any advice even if its to confirm its feasible.

AC

 

[SkipVought]

then just grab the data from the workbooks via MS Query.

faq68-5829

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

Thanks for the link I will investigate tomorrow.

AC

 

[andycapp28]

I've had a look and created 3 sheets as tables, however when I try to get external data i get message
This data source contains no visible tables.

I think even when I overcome this issue its going to take a while to understand the workings of MS query and its quirks

I am going to have to go back to trying to get a macro solution.

AC

 

[SkipVought]

This data source contains no visible tables

In the Add Tables, click the OPTIONS button and CHECK EACH BOX.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

I have to use Excel 2007 and find it difficult to find my way around the new style.

How and where do I do this Add tables, options.

I have 9 vlookups all referencing the same column for lookup reference, 5 work fine the remaining 4 are all giving me a #REF! result. I can see no difference between the first 5, can you spot anything and or explain what the #REF! refwers to. I know without the datasheet its pretty nigh impossible to see what I might be missing. I can assure you though values to be returned do exist. Is there a restriction on distance from the lookup value?

Addressee
=if(isna(vlookup(B2,TY_Certificates_Monthly_Address!A2:F62623,2,FALSE)),"",VLOOKUP(B2,TY_Certificates_Monthly_Address!A2:F62623,2,FALSE))

Salutation
=if(isna(vlookup(B2,TY_Certificates_Monthly_Address!A2:F62623,3,FALSE)),"",VLOOKUP(B2,TY_Certificates_Monthly_Address!A2:F62623,3,FALSE))

Address
=if(isna(vlookup(B2,TY_Certificates_Monthly_Address!A2:F62623,4,FALSE)),"",VLOOKUP(B2,TY_Certificates_Monthly_Address!A2:F62623,4,FALSE))

City
=if(isna(vlookup(B2,TY_Certificates_Monthly_Address!A2:F62623,5,FALSE)),"",VLOOKUP(B2,TY_Certificates_Monthly_Address!A2:F62623,5,FALSE))

County
=if(isna(vlookup(B2,TY_Certificates_Monthly_Address!A2:F62623,6,FALSE)),"",VLOOKUP(B2,TY_Certificates_Monthly_Address!A2:F62623,6,FALSE))

Postcode
=if(isna(vlookup(B2,TY_Certificates_Monthly_Address!A2:F62623,7,FALSE)),"",VLOOKUP(B2,TY_Certificates_Monthly_Address!A2:F62623,7,FALSE))

Country
=if(isna(vlookup(B2,TY_Certificates_Monthly_Address!A2:F62623,8,FALSE)),"",VLOOKUP(B2,TY_Certificates_Monthly_Address!A2:F62623,8,FALSE))

Organisation
=if(isna(vlookup(B2,TY_Certificates_Monthly_Address!A2:F62623,9,FALSE)),"",VLOOKUP(B2,TY_Certificates_Monthly_Address!A2:F62623,9,FALSE))


Here's hoping for help

 

[SkipVought]

Assuming that you have the same heading in your sheet, is Addressee, Salutation, Address...etc, AND assuming that Addressee is in C1, then...
[tt]
=if(isna(vlookup(B2,TY_Certificates_Monthly_Address!A2:J62623,MATCH(C$1,TY_Certificates_Monthly_Address!1:1,0),FALSE)),"",VLOOKUP(B2,TY_Certificates_Monthly_Address!A2:J62623,MATCH(C$1,TY_Certificates_Monthly_Address!1:1,0),FALSE))
[/tt]
and copy to all formula cells.

BTW, your LOOKUP RANGE was not defined correctly.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

And as a matter of sound design practice, strive to craft a single formula to handle as much as possible. Hence the MATCH lookup to return the column offset that VLOOKUP needs.

On the Query issue:

I have to use Excel 2007 and find it difficult to find my way around the new style.

How and where do I do this Add tables, options.

Data > Get external data > from other sources > from Microsoft Query > Un Check the box "Use the query wizard..." Databases TAB > Excel Files* > drill down to your workbook > OPTIONS in the Add Tables Window.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

Skip, I would like the formula to do it all.

Using the formula you put in your response looks perfect except I'm getting returned blank. In addition I'm unsure of what the 1:1,0 means on the MATCH

I fear I may not have explained well enough what I'm trying to achieve.

I have two sheets/tables. The first sheet holds Imp ID, Cons ID, Addressee, Salutation, Address. The second sheet holds Cons ID, Date, Amount. Using Column B2 from first sheet as lookup value to sheet2 I want to retrieve the Addressee, Salutation and Address.


AC

 

[andycapp28]

Apologies I hit submit before review my thread.

I have two sheets/tables. The first sheet holds Imp ID, Cons ID, Addressee, Salutation, Address. The second sheet holds Cons ID, Date, Amount. Using Column B2 from second sheet as lookup value to sheet1 I want to retrieve the Addressee, Salutation and Address.

 

[SkipVought]

[tt]
TY_Certificates_Monthly_Address
Imp ID, Cons ID, Addressee, Salutation, Address

Sheet2 ADD THESE HEADINGS HERE!!!
Col A, Cons ID, Date, Amount, Addressee, Salutation, Address
=if(isna(vlookup(B2,TY_Certificates_Monthly_Address!B2:J62623,MATCH(E$1,TY_Certificates_Monthly_Address!1:1,0)-2,FALSE)),"",VLOOKUP(B2,TY_Certificates_Monthly_Address!B2:J62623,MATCH(E$1,TY_Certificates_Monthly_Address!1:1,0)-2,FALSE))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

Skip,
I am sorry for just not getting this into my head!

I already have the column headings you mentioned in my sheet1 that is to hold the formula. I am getting only blanks returned when I use the vlookup/match compared to just the vlookup.

I am misunderstanding it's use. Is it an offset I have wrong or can you advise from the more specific column definitions below what the vlookup/match should be to get the addressee through to postcode?

you sent last
=if(isna(vlookup(B2,TY_Certificates_Monthly_Address!B2:J62623,MATCH(E$1,TY_Certificates_Monthly_Address!1:1,0)-2,FALSE)),"",VLOOKUP(B2,TY_Certificates_Monthly_Address!B2:J62623,MATCH(E$1,TY_Certificates_Monthly_Address!1:1,0)-2,FALSE))

I can confirm a row in TY_Certificates_Monthly(Sheet1) will find a match on Cons ID in TY_Certificates_Monthly_Address(Sheet2).

My TY_Certificates_Monthly(sheet 1) column names are
A Import ID
B Cons ID
C Fund ID
D Fund Desc
E Print Desc
F Appeal ID
G Split Amnt
H Total Smnt
I Addressee (vlookup/match needed to get data from sheet2)
J Salutation (vlookup/match needed to get data from sheet2)
K Address (vlookup/match needed to get data from sheet2)
M City (vlookup/match needed to get data from sheet2)
N County (vlookup/match needed to get data from sheet2)
O Postcode (vlookup/match needed to get data from sheet2)

My TY_Certificates_Address(sheet 2) column names are
A Cons ID
B Addressee
C Salutation
D Address
E City
F County
G Postcode

AC

 

[SkipVought]

Here's a useful tip

In any formula, you can "see" what an interior function or reference returns, by highlighting that function or reference and hitting F9. Just remember to follow that by hitting the ESC key to return the function or reference, else the resolved value remains in your formula. For instance in this case, you can see exactly what column offset is being returned here, with the addition of MINUS TWO...
[tt]
=if(isna(vlookup(B2,TY_Certificates_Monthly_Address!B2:J62623,[highlight]MATCH(E$1,TY_Certificates_Monthly_Address!1:1,0)[/highlight]-2,FALSE)),"",VLOOKUP(B2,TY_Certificates_Monthly_Address!B2:J62623,MATCH(E$1,TY_Certificates_Monthly_Address!1:1,0)-2,FALSE))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

Skip, another great tip from you, many thanks.
I've revised the formula (see below) as TY_Certificates_Address (sheet 2) has its Cons ID in Column A.

However its still showing #N/A using F9 and I can't see why. Does it need $ symbols perhaps around sheet names and cells?
It just makes no sense as B2's value in sheet 1 matches A2's value in sheet2, can you think of why as I'm really stuck.

Can you please explain the use of 1:1,0 -2 offset so i understand it for any further uses of the technique.

=if(isna(vlookup(B2,TY_Certificates_Monthly_Address!A2:J62623,MATCH(E$1,TY_Certificates_Monthly_Address!1:1,0)-2,FALSE)),"",VLOOKUP(B2,TY_Certificates_Monthly_Address!A2:J62623,MATCH(E$1,TY_Certificates_Monthly_Address!1:1,0)-2,FALSE))

Also to maintain accuracy(if I get it to work that is) I need to copy this formula down each row yes, if the sheets are not in sequence of CONS ID will it still work?

AC

 

[SkipVought]

MATCH is looking up an EXACT MATCH for the arg1 value in the arg2 range and returning an offset from the start of the range. So is the lookup value is found in the first cell of the lookup range, MATCH returns 1. READ Excel Help! The MATCH()-2 formula is to "align" this calculated value with the column offset that VLOOKUP needs in order to return the value for the corresponding heading.

Here's a simple example...
[tt]
LkupSht
Head1 Head2 Head3 Head4
Acron OH Sunny 58

My List of lookup values in Head2, in column A, returning values in Head3 & Head4 columns
A Head3 Head4
OH =VLOOKUP(A2,LkupSht!$B$2:$D$9999,MATCH(B$1,LkupSht!$1:$1,0)-1,FALSE)
[/tt]
BTW, I almost NEVER use VLOOKUP. I ususlly use INDEX & MATCH AND Named Ranges!!! SAME result...
LkupSht
Head1 Head2 Head3 Head4
Acron OH Sunny 58

My List of lookup values in Head2, in column A, returning values in Head3 & Head4 columns
A Head3 Head4
OH =INDEX(Head3,MATCH(A2,Head2,0),1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

Skip, thanks I not sure I can ask you anything more on this subject.

I can get the results I want for Addressee(ColB) and Salutation(ColC)from sheet2 returned into (ColH) and (ColI) in sheet1 but everything after that returns nothing!

If I ever resolved it I need to roll this formula across Cols H-Q for all rows without changing anything

I used this fromula in ColH Row 2 of Sheet1
for retrieving addressee
=IF(ISNA(VLOOKUP(B2,TY_Certificates_Monthly_Address!$A$1:$Q$24000,MATCH($H$1,TY_Certificates_Monthly_Address!$1:$1,0),FALSE)),"",VLOOKUP(B2,TY_Certificates_Monthly_Address!$A$1:$Q$24000,MATCH($H$1,TY_Certificates_Monthly_Address!$1:$1,0),FALSE))

When I copied it to ColI Row 2 of Sheet1 for retrieving Salutation it returned nothing until I changed it from C2 to B2 again in VLOOKUP
=IF(ISNA(VLOOKUP(B2,TY_Certificates_Monthly_Address!$A$1:$Q$24000,MATCH($I$1,TY_Certificates_Monthly_Address!$1:$1,0),FALSE)),"",VLOOKUP(B2,TY_Certificates_Monthly_Address!$A$1:$Q$24000,MATCH($I$1,TY_Certificates_Monthly_Address!$1:$1,0),FALSE))

But trying the same for J-Q it never returned anything but blanks.

If you cannot see anything obvious may I thank you very much for all your help and effort to resolve.

All the very best
AC

 

[SkipVought]

I would highly recommend using 1) Named Ranges and 2) Index & Match.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

Skip, working on the example you sent, I believed the result of the index/match OH to be Sunny from head3.

I have created names and put an =if isna/index/match in the cell I require the result in.

My lookup value is Monthly B2, my result requirement is Monthly J2 looking to receive Organisation
formula as follows;
=IF(ISNA(MATCH(B2,Address,0)),"No Match", INDEX(Monthly,MATCH(B2,Address,0),4))

I still get returned No Match, this is rubbish as all rows from Monthly colB exist in Address colA and their formats are the same (general)

Address
ConsID Addressee Salutation Organisation Addressln1
9928 Mrs J Chapman Mrs Chapman Quorn Barts 15Paddock
9983 Mr D Simpkin Mr Simpkin Rempstone Long Bank

Monthly
ImpID ConID Fund Desc Appeal Amnt Tot
250003 9928 3900 General 2004PGA £236.00 £500.00
282307 9983 1124 Hyde PC2010 £50.00 £3,600.00
Addressee Salutation Organisation
?await result ?Await result ?await result
Does these aid you to assist any further?

AC

 

[SkipVought]

Andy,

Heck of a table to parse!!!

Put some sweat into your example with a proper table!!!

FOR EXAMPLE!!! (I'm GUESSING!!!)...
[tt]
ConsID Addressee Salutation Organisation Addressln1
9928 Mrs J Chapman Mrs Chapman Quorn Barts 15Paddock
9983 Mr D Simpkin Mr Simpkin Rempstone Long Bank
[/tt]
Yer gonna have to wait while I slog thru this MESS as sort it out!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Please post your FORMULA, as I see NOTING related to ...

TY_Certificates_Monthly_Address!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

My results in about 30 seconds, after spending 10 minutes massaging your data...
[tt]
ImpID ConID Fund Desc Appeal Amnt Tot Addressee Salutation Organisation
250003 9928 3900 General 2004PGA £236.00 £500.00 Mrs J Chapman Mrs Chapman Quorn
282307 9983 1124 Hyde PC2010 £50.00 £3,600.00 Mr D Simpkin Mr Simpkin Rempstone
[/tt]
My Formula...
[tt]
Monthly!H2: [b=IF(ISNA(MATCH($B2,ConsID,0)),"NO MATCH",INDEX(INDIRECT(H$1),MATCH($B2,ConsID,0),1))[/b]
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}