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]

faq68-6659

VERY VERY important CONCEPT!

As a matter of practice, I make sure that if my lookup values are numeric identifiers, that they are ALL converted to TEXT properly.

Formatting changes NOTHING, ABSOLUTELY NOTHING in the underlying value. Numbers must be CONVERTED to text!

Skip,

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

 

[andycapp28]

Skip,

I've always found it impossible to get the likes of table and formatting into a thread. In an earlier attempt I tried offering uploaded file to media fire.

I can only apologise for latest thread leaving you a mess as you put it to sort out.

In my last thread I took your advice and created a named range called Address this covered cells A2-K150 (sheet2)

I created a a named range called Monthly this covered cells B2-G150 (sheet1)

I posted you my last attempt(formula) I was trying to return results to sheet1 Organisation ie
J2=IF(ISNA(MATCH(B2,Address,0)),"No Match", INDEX(Monthly,MATCH(B2,Address,0),4))

It's very difficult to grasp things when under pressure to deliver a result that requires uses and techniques in Excel that one is not familiar with which appear straightforward until you put them into action with live data. Hence my dilemma now as I cannot resolve even with your help for which I have and am duly grateful for.

Maybe I should just give up and key the results in manually?

AC

 

[SkipVought]

With the INDEX & MATCH method EACH and EVERY Named Range is ONLY ONE COLUMN

As you see from my formula...
[tt]
=IF(ISNA(MATCH($B2,ConsID,0)),"NO MATCH",INDEX(INDIRECT(H$1),MATCH($B2,ConsID,0),1))
[/tt]
In particular, INDIRECT(H$1), in the INDEX formula uses the HEADING in row 1 in column H, and CONVERST to a RANGE, using the INDIRECT function. THAT range, in this column Addressee in the next column, Salutation, and in the last column, Organisation, should EACH refer to the Data in the corresponding Named Ranges on the Address sheet, where you are LOOKING UP the data.

Your MONTHLY named range is totally IRRELEVANT!

Using this technique, INDEX want ADDRESS in column H, SALUTATION in column I and Addressee in column J. Hence the INDIRECT to achieve that using only ONE consistent formula for everything.

Furthermore, using MATCH(B2,Address,0) in nonsence! Does B2 have an Address? NO! B2 has a ConsID value, so you want to find a matching value in the ConsID Named Range on your Address sheet.

Using this technique, you have no need for multiple column ranges!!!

On the Address sheet...
1. SELECT all your data including headings.

2. Insert > Name > Create -- CREATE NAMES IN TOP ROW.

Now you have named ranges for each column of data.


Skip,

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

 

[andycapp28]

Thanks

Are you saying both of my worksheets have to have create names in top row?

My first test resulted in #name?

When I returned to the version using the ugly alternative
=if(ISNA(VLOOKUP, MATCH etc and substituted in the column number from the address sheet I was looking to fill it worked perfectly.

Fingers crossed maybe this will be the last thread you'll get from me once I've resolved why a #name? error

AC

 

[SkipVought]

No.

On the sheet that you have your lookup data on, which I would assume if the Address sheet, you perform, using menu items Insert > Name > Create - Create names in TOP row selecting the TOP checkbox ONLY.

When I returned to the version using the ugly alternative
=if(ISNA(VLOOKUP, MATCH etc and substituted in the column number from the address sheet I was looking to fill it worked perfectly.

Yes, it will, but the formula in each column is DIFFERENT. That DIFFERENCE (the NUMBER OFFSET for the correct value) will at some point bite you.

I strive for a simple approch that will ALWAYS work, even when some things get screwed up, like someone inserting a column in the lookup data sheet.


Skip,

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

 

[andycapp28]

Skip,
many many thanks its at last doing what I wanted.

There is one final issue I have and that is on the County column, some are blank. I used ISBLANK on the vlookup but appears not to work in MATCH. It returns a zero?

AC

 

[SkipVought]

Simplest way is to use the conditional format feature in Excel, to make the FONT color the same as the cell interior color, when a cell in that column equals zero.

Skip,

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

 

[andycapp28]

That's a fine solution but if I want to use the sheet as a mailmerge datasource will it print the 0 do you think?

 

[SkipVought]

Yes.

A much more robust approch would be MS Query on a new sheet, joining the Monthly and Address on the ConsID.

Select *
From [Address$] A, [Monthly$] B
Where A.ConsID=B.ConsID

Skip,

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

 

[SkipVought]

You could also do this to trap the ZERO...
[tt]
Monthly!H2: =IF(ISNA(MATCH($B2,ConsID,0)),"NO MATCH",IF(INDEX(INDIRECT(H$1),MATCH($B2,ConsID,0),1)=0,"",INDEX(INDIRECT(H$1),MATCH($B2,ConsID,0),1)))
[/tt]

Skip,

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

 

[andycapp28]

Thank You Skip I will look into trying both those offerings.

Many thanks for all your ongoing support and perseverence on my behalf it's been invaluable and very much appreciated.
Boy do you know your stuff!!


AC

 

[Gavona]

Hey Andy, How about you click on the thing at the foot of Skip's last post:

[red]*[/red][blue]Thank SkipVought
for this valuable post![/blue]

Gavin

 

[andycapp28]

Skip,

Now having understood the Index/Match solution I would like to progress with trying the sql one.

I have created in the workbook a separate list(table) for each worksheet, ie Fund Description, Address, Monthly. I am unable to name these so I do not see how I can reference them in the Sql to enable a join?

I can see I can create a datasource to this workbook.

I can start a new database query and point it to the datasource.

Looking back over your threads I can't see anything I've missed.

AC

 

[SkipVought]

FYI: Each time you do a query using Data > Import data > Other Sources ... it ADDs a QueryTable object to your sheet. You really only want ONE QueryTable object on your sheet, although you can have more than one, but you must be careful about how you use multiples.

Once a QT has been added to a sheet, the Connection String can be modified via code and the Command Text (SQL) can be modified if necessary, by EDITING the query or via code.

Having stated that, in the query creation process, when you get to ADDING a TABLE to you query, and you cannot see your workbook sheets in the list of tables to add, hit the OPTIONS button, and check all the boxes. Your sheets as Fund Description$, Address$, Monthly$ will appear.

Skip,

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

 

[andycapp28]

Skip,

Excel 2003

Data> Import External Data> New dataabase Query > Choose Datasource > I selected New Datasource > Named it and chose Driver do MS xls > Clicked Connect and Selected my Workbook > The default table box confusing as it showed my table names twice? > Hit OK a couple of times and Query displayed my chosen table

Am I OK so far?

 

[SkipVought]

Don't understand why your tables (sheets) are named twice.

But keep going.

Skip,

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

 

[andycapp28]

Thanks Skip,

OK, so I get into the SQL now.

The drop down boxes for field names are way too small which is very unhelpful.

Anyhow jump that bit because the query results are getting interesting.

I have a 20" screen yet cannot expand the SQL coding space, its very small!

Adding third table TY Fund Description gives me a syntax error "expecting 3 parms"? when I look at SQl code it has added the table in twice and not allocated it an alias of C.

So once I've saved this query I can run it repeatedly against different data sets so long as I keep the same column and table names, yes?

From the Query results can they be used in a Word mailmerge do you know?

 

[SkipVought]

I have a 20" screen yet cannot expand the SQL coding space, its very small!

I routinely COPY the SQL and PASTE into a NotePad to manupulate.

MS Query does not have a very good editor.

Adding third table TY Fund Description gives me a syntax error "expecting 3 parms"? when I look at SQl code it has added the table in twice and not allocated it an alias of C.

Rather than guessing at what you did, please post your SQL with the appropriate comments/emphasis to focus the issue(s)




Skip,

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

 

[andycapp28]

Not to worry about the issue.

My time using UNIX and VI should have led my thought process to using an alternative text pad.

Thanks again Skip for all this help

AC

 

[SkipVought]

No problem. Glad to see you're grabbing on to some new tools!

Skip,

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