VBA for pivot like table (but not pivot)

[JackDanIce]

Hi,

With some VBA, I collect the information in columns A and B and then create column D for the unique items and the names of the sheets they were found in from E1 across.

What I would now like to do is fill in the yellow area with something simple like an "x" to denote if it exists in that sheet and blank if not. Can someone help me with some code to do this please?


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 87px"><COL style="WIDTH: 65px"><COL style="WIDTH: 56px"><COL style="WIDTH: 92px"><COL style="WIDTH: 87px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Item</TD><TD style="FONT-WEIGHT: bold">Sheet Name</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="FONT-WEIGHT: bold">Unique Item</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>A</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD>A</TD><TD style="BACKGROUND-COLOR: #ffff00">x</TD><TD style="BACKGROUND-COLOR: #ffff00">x</TD><TD style="BACKGROUND-COLOR: #ffff00">x</TD><TD style="BACKGROUND-COLOR: #ffff00">x</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>A</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ffff00">x</TD><TD style="BACKGROUND-COLOR: #ffff00">x</TD><TD style="BACKGROUND-COLOR: #ffff00">x</TD><TD style="BACKGROUND-COLOR: #ffff00">x</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>A</TD><TD style="TEXT-ALIGN: right">3</TD><TD> </TD><TD>C</TD><TD style="BACKGROUND-COLOR: #ffff00">x</TD><TD style="BACKGROUND-COLOR: #ffff00">x</TD><TD style="BACKGROUND-COLOR: #ffff00">x</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>A</TD><TD style="TEXT-ALIGN: right">4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>B</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>B</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>B</TD><TD style="TEXT-ALIGN: right">3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>B</TD><TD style="TEXT-ALIGN: right">4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>C</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>C</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>C</TD><TD style="TEXT-ALIGN: right">3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

I'm thinking I need to use nested loops to loop through column A and across row 1 (from E1 onwards) and use some sort of:

IF item = unique item AND sheet name = E1 then x

But I'm getting lost coding this.

Thank you in advance,
Jack

 

[JackDanIce]

Bump

 

[jim may]

Although you say ("but not pivot") - I created a PT based on your Columns A & B; Making some other setting-adjustments - the final results (PT) in D:H look pretty much like what you want...

Excel Workbook
	A	B	C	D	E	F	G	H
1	Item	Sheet Name		Unique Item
2	A	1			1	2	3	4
3	A	2		A	x	x	x	x
4	A	3		B	x	x	x	x
5	A	4		C	x	x	x
6	B	1
7	B	2
8	B	3
9	B	4
10	C	1
11	C	2
12	C	3
Sheet1

Excel 2007

 

[RoryA]

Can't you just add formulas to the table to calculate if the count is greater than 0?

 

[JackDanIce]

Jim - thanks for the suggestion, but very much so need to avoid pivot tables in this requirement

Rory - can you give me an example of the formula please? I should be able to take it from there

 

[RoryA]

=IF(SUMPRODUCT(--($A$2:$A$12=$D2),--($B$2:$B$12=E$1))>0,"X","")
should work.

 

[JackDanIce]

Works a charm, thank you Rory