Transpose variable Contact List

PoohsMate

Board Regular
Joined
Aug 9, 2005
Messages
123
I have created the follwoing based on different formulas from other posts.

There is a list of contact details I have scanned into Word then validated and copied into Excel. There are names, addresses, tel nos and other info. Unfortuately not all are complete so its a bit of a dogs breakfast.

I am trying to get them as uniform as possible so that I can then transfer them into Access and start to create a porper contact database.

In the Excel stage all appears to work OK for the first set of records but then produces the #N/A error. Can anyone please help guide me as to what I am doing wrong.

Bernard
From Word Xerox Draft2.xls
ABCDEF
1Data1TransRecordfldOrganisationfld2
2CareDirectGloucestershire11CareDirectGloucestershireFreepost
3Freepost12#N/A#N/A
4SWC457213#N/A#N/A
5GLoucester14#N/A#N/A
6GL12ZZ15  
7Bus:080044400016#N/A 
8BusinessFax:0145242735917#N/A#N/A
9E-mail:caredirect@gloscc.gov.uk18#N/A#N/A
10 9#N/A#N/A
11CaringConnectionsLimited210#N/A#N/A
Sheet1
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Remove the 1 in B1 and try this normal (non-array) formula:

Code:
=IF((COLUMN()-COLUMN($E$1))<COUNTIF($B:$B,$D2),INDEX($A:$A,MATCH($D2,$B:$B,0)+COLUMN()-COLUMN($E$1),0),"")
 

PoohsMate

Board Regular
Joined
Aug 9, 2005
Messages
123
Andrew

Have removed 1 from B1 - (Result in E2 through to K2 changed to #N/A)

Where do I put the non array formula please?

Bernard
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
PoohsMate said:
Andrew

Have removed 1 from B1 - (Result in E2 through to K2 changed to #N/A)

Where do I put the non array formula please?

Bernard

In E2 copied down and across.
 

PoohsMate

Board Regular
Joined
Aug 9, 2005
Messages
123

ADVERTISEMENT

Andrew
I expect I am making a simple error which is resulting in the #N/A persisting. Can you help any further please?

Bernard
From Word Xerox Draft2.xls
ABCDEF
1DataTransRecordfldOrganisationfld2
2CareDirectGloucestershire11#N/A#N/A
3Freepost12#N/A#N/A
4SWC457213#N/A#N/A
5GLoucester14#N/A#N/A
6GL12ZZ15#N/A 
7Bus:080044400016#N/A#N/A
8BusinessFax:0145242735917#N/A#N/A
9E-mail:caredirect@gloscc.gov.uk18#N/A#N/A
10 9#N/A#N/A
11CaringConnectionsLimited210#N/A#N/A
12TheWheelhouse211#N/A 
13Bondsmill212#N/A#N/A
14Stonehouse213#N/A#N/A
15stroud214#N/A#N/A
16Glos215#N/A#N/A
17GL1O3RF216#N/A#N/A
18FullName:MrsDRobinson2
19Bus:014538233002
Sheet1
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Make sure your entries in column D are numbers not text. You can chack with the ISNUMBER function.

If they are text you can convert them by formatting as eg General, then choosing Data|Text to Columns and clicking Finish.
 

PoohsMate

Board Regular
Joined
Aug 9, 2005
Messages
123
Andrew,
Thank you for your help with this.
Your last sugestion solved my remaining #N/A problem-
General
Data
Text to columns
Finish

Kind regards
Bernard
 

Watch MrExcel Video

Forum statistics

Threads
1,118,055
Messages
5,569,946
Members
412,299
Latest member
agentless
Top