Too much repetitive data

[jwheeler388]

Example attached. I would like to take duplicate rows and remove the duplicate info but have all of the unique data added to the same row. Can anyone help me I am pretty new to Excel. Thanks in advance for your time!

[mikeTRON]

Welcome to the forum.

So we can possibly help but your sample is not clear to me.Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[CAntosh]

MikeTRON is right about your sample - I'm not sure I fully understood it, but based on the assumption that the bottom bit was your "After" demonstration, I came up with the following, which appears to work:

Note 1: Array formulae need to be confirmed with Ctrl + Shift + Enter instead of the regular Enter.
Note 2: I corrected a few errors, namely that your columns were mislabeled near the end of your "After" example and you misspelled "Management" in your table.
Note 3: I built my solution in sheet 2 (See attached)

In A2, array formula:
=IFERROR(INDEX(Sheet1!$A$2:$A$12,MIN(IF(COUNTIF($A$1:$A1,Sheet1!$A$2:$A$12)=0,ROW(Sheet1!$A$2:$A$12)))-1),"")

In B2, non-array, filled right through F2:
=IFERROR(INDEX(Sheet1!$A$2:$K$12,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$12,0),MATCH(Sheet2!B$1,Sheet1!$A$1:$K$1,0)),"")

In G2, non-array:
=IFERROR(IF(COUNTIFS(Sheet1!$G$2:$G$12,Sheet2!G$1,Sheet1!$A$2:$A$12,Sheet2!$A2)>0,G$1,""),"")

In H2, array, filled right through K2:
=IF(G2<>"",INDEX(Sheet1!H$2:H$12,MATCH(1,(Sheet1!$G$2:$G$12=Sheet2!$G2)*(Sheet1!$A$2:$A$12=Sheet2!$A2),0)),"")

You can then repeat the G2 & H2:K2 formulas for the other departments, changing the G2 to L2, etc. to match the new department. You can then fill all of the formulae down as far as you think you need, and Sheet2 should automatically update as you add/subtract/change entries in sheet1.

My formulae are designed for your sample, so they cover rows 2:12, for more rows change all of the $A$2:$A$12,$G$2:$G$12, etc. entries to something bigger, like $A$2:$A$200, or whatever suits your data.

[jwheeler388]

MikeTRON is right about your sample - I'm not sure I fully understood it, but based on the assumption that the bottom bit was your "After" demonstration, I came up with the following, which appears to work:

Note 1: Array formulae need to be confirmed with Ctrl + Shift + Enter instead of the regular Enter.
Note 2: I corrected a few errors, namely that your columns were mislabeled near the end of your "After" example and you misspelled "Management" in your table.
Note 3: I built my solution in sheet 2 (See attached)

In A2, array formula:
=IFERROR(INDEX(Sheet1!$A$2:$A$12,MIN(IF(COUNTIF($A$1:$A1,Sheet1!$A$2:$A$12)=0,ROW(Sheet1!$A$2:$A$12)))-1),"")

In B2, non-array, filled right through F2:
=IFERROR(INDEX(Sheet1!$A$2:$K$12,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$12,0),MATCH(Sheet2!B$1,Sheet1!$A$1:$K$1,0)),"")

In G2, non-array:
=IFERROR(IF(COUNTIFS(Sheet1!$G$2:$G$12,Sheet2!G$1,Sheet1!$A$2:$A$12,Sheet2!$A2)>0,G$1,""),"")

In H2, array, filled right through K2:
=IF(G2<>"",INDEX(Sheet1!H$2:H$12,MATCH(1,(Sheet1!$G$2:$G$12=Sheet2!$G2)*(Sheet1!$A$2:$A$12=Sheet2!$A2),0)),"")

You can then repeat the G2 & H2:K2 formulas for the other departments, changing the G2 to L2, etc. to match the new department. You can then fill all of the formulae down as far as you think you need, and Sheet2 should automatically update as you add/subtract/change entries in sheet1.

My formulae are designed for your sample, so they cover rows 2:12, for more rows change all of the $A$2:$A$12,$G$2:$G$12, etc. entries to something bigger, like $A$2:$A$200, or whatever suits your data.

You were correct about the example the bottom was supposed to be the after sorry for the typos. Thank you for the quick response. I will update once I have a chance to test the formulas you provided.

[José Augusto]

Hi
I suppose you want to avoid too many repetitions of data.

If so should not build a table but two tables.

The first table would contain data on the office location

(Office number, Office name, Office address, Office city, Office state, Office zip code)

and the other table contain the remaining data (just repeating the 'Office number'), this is

(Office number, Department, Contact title, Contact, Contact phone, Contact email)

For the first table you can use the remove duplicates.