How to merge duplicate rows?

Question

Hello,
I have an Excel table where I have the columns "salutation", "last name", "first name" and their respective orders. However, I have a lot of duplicates in the names and first names. For example, Mr. Dupont, who ordered three pairs of pants, appears in the table as follows:

Mr- Dupont- André- brown pants
Mr- Dupont André- black pants
Mr - Dupont - André - green pants

How can I have it show only:

Mr- Dupont- André- brown pants
- black pants
- green pants

Otherwise, in my pivot table, the data is incorrect since it counts the duplicates in the names.

Moreover, some people have the same last name but not the same first name!!
I started to do it manually but I have 46,000 orders so I've been at it since this morning and I haven't even finished the A's.
Could someone have a formula to actually concatenate rows instead of columns?
Thank you very much!

Configuration: Windows XP Internet Explorer 6.0

wanajo38 · Accepted Answer

In fact, it's even pointless; the pivot table will do that on its own, you just need to build it correctly.  ++ -- Wanajo38

wanajo38 · Answer

Hi,

I suggest assigning a customer number to each client to facilitate the management of the pivot table.

First, if I were to do it, I would identify the clients with duplicates (using a simple filter) and assign them their customer number. Then, I would just need to assign a customer number to the others.

Then, the pivot table will take care of the rest easily.

I love pivot tables, if you want, you can send me a sample file and I'll make a proposal for my solution.

See you soon

--
Wanajo38

astridvanwe · Answer

Thank you for your quick reply!!

Could you simply explain to me more precisely how I "identify clients with duplicates using a simple filter"?
I am completely new to this.... I have already concatenated the last name and the first name into a column "last+first name". This way, I grouped all the duplicates, but then how do I assign them a number? Should I merge so that the same Mr. André Dupont who appears 3 times doesn't have 3 different client numbers???
Could you go into a bit more detail because I am really bad at Excel...
Thank you a thousand times!!

wanajo38 · Answer

What is your version of Excel after all??? -- Wanajo38

astridvanwe · Answer

Microsoft Excel 2003 !

wanajo38 · Answer

I have the 2007 version in front of me so it's not going to be easy, but I'll try something and I'll be back. -- Wanajo38

wanajo38 · Answer

I found it, sorry for the wait...

It's in Excel 2007, so you'll need to adapt it to Excel 2003.

You select the column where you have the duplicates. You do a conditional formatting. You choose the "formula is" option and you type =COUNTIF(A:A,A1)>1 if it's your column A of course. You choose a yellow format for example. Then you filter the column to only show the yellow cells and you'll have just the duplicates.

But I think you don't need that and your pivot table should do the job by itself. Upload a sample file to http://www.cijoint.fr/ and send me the link and I will see what I can do.

++
--
Wanajo38

wanajo38 · Answer

No but empty it and put anything in its place... we don't care about the data... lol (save it under a different name first so you don't mess things up^^) -- Wanajo38

wanajo38 · Answer

Otherwise, how do you, as a novice, create a Pivot Table??? It's not that simple... I think the problem lies here. Normally, your Pivot Table does exactly what you ask, that's what it's made for, to analyze data.

See you later
--
Wanajo38

wanajo38 · Answer

lol...  -- Wanajo38

wanajo38 · Answer

Hey !!! Are you kidding me ^^ it's super easy.

You select your table and rename it "table1" for example (in the top left where the name of your cell is located).

Then you insert a Pivot Table. You set "first name+last name" as row label and "Total amount including tax excluding fees" as value. You will then get the total amount including tax per person. You can then add, for example, "Product family" as a row label and it will give you the breakdown by person and by product.

In fact, it's a pity you're not on Excel 2007, it would be even simpler.

Anyway, your issue clearly lies in the fact that you don't properly understand Pivot Tables. I'm not an expert either, but I'm just telling you what I think. You need to define precisely what you want to do and I'll tell you how to arrange the elements in your Pivot Table. In any case, no problem, it's doable.

@+ have a nice weekend
--
Wanajo38

wanajo38 · Answer

http://www.cijoint.fr/cjlink.php?file=cj200912/cijBwfiPT0.xls

Here is an example of what we can do...

You can delete your last two columns, they're useless and add a gender column.

Alright, have a good weekend, I'm heading back.

See you later
--
Wanajo38

How to merge duplicate rows?

12 answers

No "pin - windows hello" prompt at windows 10 startup

Identifying the source of an mp3 problem?

Keyboard on network x

Texture issues in enshrouded

Problem with my sound!

Error 3005 on france tv

Strange clicking sound hard drive

Fibre optic strapping advice request

Review of the lg ultragear 27gr93u for xbox series x?

List numbering style