Extracting entries for column headers

[kyle_04]

Hi,

I am trying to create a summary sheet that does not require any manual inputting. The problem I have is with the column headers linked to the formulas. Instead of having to manual input these I would like to extract them from an array in another sheet.

So, is it possible to extract the first entry in the array, then the second, and then the third, and so on. and will these be displayed in alphabetical order or whenever the first entry is found? Ignoring duplicate entries of course.

So for example I need the formula for the summary column header in A1, b1, c1, (and so on) extracting data from sheet2!A:A with entries such as:

Kyle
John
Smith


Your help would be much appreciated,

Kyle

 

[Peter_SSs]

The description is a bit vague but if Kyle, John, Smith is in A1:A3 of Sheet 2 and you want those in A1:C1 of another sheet, then in the other sheet ..
A1: =INDEX(Sheet2!$A:$A,COLUMNS($A1:A1))
Then copy this formula acroos to B1, C1 etc

 

[kyle_04]

Hi,

Thanks for the reply,

That seems to have worked, but when I apply it to my data zeros show up in the first three columns and some of the entries are duplicated.

Also I need to expand the formula to include data from another column in sheet 2!B:B, which will be last names that need to be displayed on the summary in row 2. Can this be done?

In essence the summary will show:

A B C
1 Kyle John Smith
2 Donnelly Turner Bate




Thanks,

Kyle

 

[Peter_SSs]

This seems a much more complex problem and I doubt easily solved by formulas alone.

For example, you don't want duplicates from column A but since you now say you also want the Last names from column B. So, if the names in columns A:B were ..

John Smith
Ann Jones
John Brown

.. even though John is a duplicate in column A, wouldn't you want that name twice in your final list?

For a manual approach, ensure these columns have headers and look at Advanced Filter to produce a unique list of names. Then look at using that list for your new headers, perhaps using the sort of formula I suggested earlier.

Otherwise a macro could be used to do these tasks.

Post back if you need more help with either method and include what Excel version you are using.

 

[kyle_04]

Hi,

I have got round the issue by combining the columns in sheet2,

However it still doesn't explain the zero's and duplicates and there are also some entries missing. I have used the formula over 14 columns and starts like this:

=INDEX(sheet2!$E$10:$E$111,COLUMNS(Summary!$B27:B27))


Do you think it is because there are blanks in the array (sheet2!$E$10:$E$111).

Much appreciated,

Kyle

 

[kyle_04]

Hi,

I have just checked and that formula is picking up all the entries with blanks in and showing them as 0, and it is keeping the duplicates in, is there any way round this?


Regards,

Kyle

 

[Peter_SSs]

See if this is any use.

Sheet2
I've used columns J:K as helper columns but any columns could be used.
J1 houses a 0.
Formulas in C2, J2 copied down to the end of your data.
Columns J:K could be hidden.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	First	Last	Both							0	4
2	John	Smith	John Smith							1
3	Ann	Jones	Ann Jones							2
4	John	Brown	John Brown							3
5	Ann	Jones	Ann Jones							3
6	John	Smith	John Smith							3
7	Bill	Smith	Bill Smith							4
8
Sheet2

Summary sheet.
Formula in A1 copied across.

Excel Workbook
	A	B	C	D	E
1	John Smith	Ann Jones	John Brown	Bill Smith
Summary

 

[kyle_04]

Hi,

Much appreciated for your replies.

I have come across this thread which has seemed to have worked without the need for additional columns:

http://www.mrexcel.com/forum/showthread.php?t=518138


Again thanks,


Kyle