Transpose variable Contact List

[PoohsMate]

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
	A	B	C	D	E	F
1	Data	1	Trans	Record	fldOrganisation	fld2
2	CareDirectGloucestershire	1		1	CareDirectGloucestershire	Freepost
3	Freepost	1		2	#N/A	#N/A
4	SWC4572	1		3	#N/A	#N/A
5	GLoucester	1		4	#N/A	#N/A
6	GL12ZZ	1		5
7	Bus:0800444000	1		6	#N/A
8	BusinessFax:01452427359	1		7	#N/A	#N/A
9	E-mail:caredirect@gloscc.gov.uk	1		8	#N/A	#N/A
10				9	#N/A	#N/A
11	CaringConnectionsLimited	2		10	#N/A	#N/A
Sheet1

 

[Andrew Poulsom]

Remove the 1 in B1 and try this normal (non-array) formula:

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

 

[PoohsMate]

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]

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]

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
	A	B	C	D	E	F
1	Data		Trans	Record	fldOrganisation	fld2
2	CareDirectGloucestershire	1		1	#N/A	#N/A
3	Freepost	1		2	#N/A	#N/A
4	SWC4572	1		3	#N/A	#N/A
5	GLoucester	1		4	#N/A	#N/A
6	GL12ZZ	1		5	#N/A
7	Bus:0800444000	1		6	#N/A	#N/A
8	BusinessFax:01452427359	1		7	#N/A	#N/A
9	E-mail:caredirect@gloscc.gov.uk	1		8	#N/A	#N/A
10				9	#N/A	#N/A
11	CaringConnectionsLimited	2		10	#N/A	#N/A
12	TheWheelhouse	2		11	#N/A
13	Bondsmill	2		12	#N/A	#N/A
14	Stonehouse	2		13	#N/A	#N/A
15	stroud	2		14	#N/A	#N/A
16	Glos	2		15	#N/A	#N/A
17	GL1O3RF	2		16	#N/A	#N/A
18	FullName:MrsDRobinson	2
19	Bus:01453823300	2
Sheet1

 

[Andrew Poulsom]

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]

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