Grouping records together using a primary key style field

[chrisnichols87]

I'm trying to set up a barcode reader, which uploads enetered data into
an excel spreadsheet. The data is copied into excel in sererate
records, but I am struggling to figure out a way to group seperate
records into one single record if they have the same barcode number.
E.g:

Barcode Quantity
AA 2
DD 7
KK 9
AA 44
DD 12

I want this to be automatically changed to:

Barcode QuantityA QuantityB QuantityC
AA 2 44
DD 7 12
KK 9

Is this even possible???

Any help would be appreciated!!!

Thanks,
Chris

 

[smw226 via OfficeKB.com]

Hi Chirs,

Have a look at pivot tables.

This will easilly be able to do what you want.

HTH

Simon

 

[chrisnichols87]

That's great, thanks a lot Simon

Chris

Hi Chirs,

Have a look at pivot tables.

This will easilly be able to do what you want.

HTH

Simon


--

 

[smw226 via OfficeKB.com]

Hi Chris,

I just looked at this again and I am glad that I did!

You can use pivot tables in a slightly stange way but it won't tell you
anywhere how to....

if you create a pivot table on your data and have barcode as your row and
quantity in both colum and Data.

This will give you the following:
AA 2 44
DD 7 12
KK 9


If you copy the table (without any totals or column headings and paste the
values into a new sheet, select all of your new data and run the code below
it will move everything along for you....NB: You may have to run this a
couple of times to get it all correct.

Sub deletetheblanks()
For Each Cell In Selection
If Cell.Value = "" Then
Cell.Delete Shift:=xlToLeft
End If
Next Cell
End Sub


HT is of more H!

Simon

I'm trying to set up a barcode reader, which uploads enetered data into
an excel spreadsheet. The data is copied into excel in sererate
records, but I am struggling to figure out a way to group seperate
records into one single record if they have the same barcode number.
E.g:

Barcode Quantity
AA 2
DD 7
KK 9
AA 44
DD 12

I want this to be automatically changed to:

Barcode QuantityA QuantityB QuantityC
AA 2 44
DD 7 12
KK 9

Is this even possible???

Any help would be appreciated!!!

Thanks,
Chris

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200611/1

 

[chrisnichols87]

I've got a slight problem. I want to select every cell in the two
columns when creating a pivot table. When doing this however, the
following is produced:


Count of 0
AA Total
AA 1
DD 2
HH 1
TT 2
(blank)
Grand Total 6



and not:



Sum of 0
AA Total
AA 78
DD 7
HH 7
TT 11
Grand Total 103


Any idea how I can do this?

Cheers,

Chris

 

[smw226 via OfficeKB.com]

Hi Chirs,

Pivot tables do take a bit of getting used to.

In the data field...if you right click and select "Field Settings" you can
change how the data is analysed.

I've got a slight problem. I want to select every cell in the two
columns when creating a pivot table. When doing this however, the
following is produced:

Count of 0
AA Total
AA 1
DD 2
HH 1
TT 2
(blank)
Grand Total 6

and not:

Sum of 0
AA Total
AA 78
DD 7
HH 7
TT 11
Grand Total 103

Any idea how I can do this?

Cheers,

Chris

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200611/1

 

[Roger Govier]

Hi Chris

Work on a Copy of your data!!

You could do it in stages.
First sort all your data by Barcode
In cell C2 enter
=IF(A3=A2,B3,"")
in cell D2 enter
=IF(AND(A3=A2,A4=A3),B4,"")
Copy C22 down for the extent of data in column A.

Data>Filter>Autofilter>use dropdown on column C and Select (Blanks)
Mark the Visible rows and right click>Delete