How to join different information from duplicated addresses (rows)

[David_withington]

Hi,


I have a spreadsheet with 6000 duplicated addresses. On the sheet column U is work to be carried out: so 3 duplicated addresses will say in column U (works to be carried out) Boiler on the row below will be kitchen and the next bathroom.


I need to take that information from the 3 rows and put it into one row and delete/hide the duplicated addresses. I can add 3 seperate columns if easier named boiler, kitchen, bathroom.


I have tried a Vlookup formula and also tried an IF statement and the merging cells but I cannot crack this problem.


If someone can help it would be much appreciated

 

[Luke M]

Could you post a sample of what your data looks like (and what you want it to look like)? I'm having trouble understanding the layout.

 

[David_withington]

How it looks

[pre]

UPRN Address          Work to be carried out
123  8 Harbour        Boiler
123  8 Harbour        Kitchen
123  8 Harbour        Bathroom
211  7 Links          Boiler
211  7 Links          Kitchen
312  21 West          Boiler
312  21 West          Kitchen
312  21 West          Bathroom
How I want it to look

UPRN  Address        Boiler  Kitchen  Bathroom
123  8 Harbour          Y       Y        Y
211  7 Links            Y       Y        N
312  21 West            Y       Y        Y

[/pre]
Hope this makes more sense

 

[David_withington]

that looked so much better when i was writing it out.


Do you need it re done?

 

[SirJB7]

Hi, David_withington!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...

Give a look at this file:

https://dl.dropbox.com/u/60558749/How%20to%20join%20different%20information%20from%20duplicated%20addresses%20%28rows%29%20%28for%20David_withington%20at%20chandoo.org%29.xlsx


Assuming your input data is in columns A:C from row 1 in advance and your output data is in columns E:I with titles in row 1, do this:


a) in E2 type this array formula: =SI.ERROR(INDICE(A$2:A$9;COINCIDIR(0;CONTAR.SI(E$1:E1;A$2:A$9);0));"") -----> in english: =IFERROR(INDEX(A$2:A$9,MATCH(0,COUNTIF(E$1:E1,A$2:A$9),0)),"")


b) in F2 type this formula: =SI(E2<>"";BUSCARV(E2;A:B;2;FALSO);"") -----> in english: =IF(E2<>"",VLOOKUP(E2,A:B,2,FALSE),"")


c) in G2 type this array formula: =SI($E2<>"";SI(ESERROR(INDICE($A$2:$A$9&$B$2:$B$9&$C$2:$C$9;COINCIDIR($E2&$F2&G$1;$A$2:$A$9&$B$2:$B$9&$C$2:$C$9;0)));"N";"Y");"") -----> in english: =IF($E2<>"",IF(ISERROR(INDEX($A$2:$A$9&$B$2:$B$9&$C$2:$C$9,MATCH($E2&$F2&G$1,$A$2:$A$9&$B$2:$B$9&$C$2:$C$9,0))),"N","Y"),"")


d) copy down E2 as required


e) copy down F2 as required


f) copy across G2 thru H2:I2


g) copy down G2:I2 as required


Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.


Just advise if any issue.


Regards!

 

[Luke M]

Another way to build this would be to use a PivotTable. Here's how I did it:


Select all of your Data, Insert - PivotTable.

Put the UPRN field into the Row Labels, then the Address field into row labels.

Put Work to be carried out in Column Labels, and then also into Value

Right-click on "Count of Work to be carried out". Field settings, number format. Change format to:

"Y";;"N"


In worksheet area, right-click on PivotTable, PT options. On Layout tab, in the "For empty cells" options, put a 0.

Now, to clean up some formatting. Remove all the subtotals and grand totals.

On pivotTable Tools - Design ribbon, Report layout, choose Tabular.

On PivotTable tools - Options ribbon, uncheck the +/- buttons from the show/hide group. When done, report will look like this:

[pre]

Count of Work to be carried out		Work to be carried out
UPRN	Address	     Bathroom	Boiler	Kitchen
123	8 Harbour	Y	Y	Y
211	7 Links	        N	Y	Y
312	21 West	        Y	Y	Y

[/pre]
which is pretty close to what you wanted.

 

[3G]

Sweet! I came up with a Pivot Table similar to Luke. I'm becoming an Excel Ewok!

 

[Luke M]

I'm becoming an Excel Ewok!

I think that sentence just made my day. =D