condense data based on value

Question

Hi Folks,

I have data in column a.  Columns c - i may contain data that I want to move up and remove duplicate instances of the value in a.  So I'm trying to make this...

item 1 | 20

item 2 | 100

item 3 | 100

item 4 | 300

item 1 | | | | 20

item 2 | | | 200

item 3 | | | | | 250

item 4 | | | | | | 0

into this...

item 1 | 20 | | | 20

item 2 | 100 | | 200

item 3 | 100 | | | | 250

item 4 | 300 | | | | | 0

Answer 1

Thanks Doug, but it's turning this...

code	name	m	t	w	th	f	s	s
0000	Item 1	0
0107	Item 2	20
0520	Item 3	350
0530	Item 4	350
0550	Item 5	350
0000	Item 1				0
0107	Item 2				20
0520	Item 3				0
0530	Item 4				0
0550	Item 5				0

into this...

code	name	m	t	w	th	f	s	s
0000	Item 1	0
0107	Item 2	20
0520	Item 3	350
0530	Item 4	350
0550	Item 5	350

when I'm looking for this...

code	name	m	t	w	th	f	s	s
0000	Pick Up Soil	0			0
0107	Laundry Bags	20			20
0520	Sheet - Queen Flat	350			0
0530	Sheet - King Flat	350			0
0550	Pillowcase	350			0

Answer 2

Assuming that there are column headers in row 1, the following should do it

Dim stritem As String

Dim strDelete As String

Dim varDelete As Variant

Dim i As Long, j As Long

strDelete = ""

With Sheets(1).Range("A1")

    For i = 1 To .CurrentRegion.Rows.Count - 1

        stritem = .Offset(i, 0)

        For j = i + 1 To .CurrentRegion.Rows.Count - 1

            If .Offset(j, 0) = stritem Then

                .Offset(i, 2) = .Offset(i, 2) & .Offset(j, 2)

                strDelete = strDelete & "|" & j + 1

            End If

        Next j

    Next i

End With

varDelete = Split(Mid(strDelete, 2), "|")

For i = UBound(varDelete) To LBound(varDelete) Step -1

    Sheets(1).Rows(varDelete(i)).EntireRow.Delete

Next i

Answer 3

Thanks Ashish,

Is there any way that can be setup in a macro?

Answer 4

Hi,

I have solved this question using Data > Get & Transform.  You may download the workbook from here.

Hope this helps.

Answer 5

Use advanced filters to create a unique list of values from column A, pasting them into column K starting in Row 2. Then in L2, use the formula

=IFERROR(INDEX(B$1:B$200,MIN(IF($A$1:$A$2002=$K2,IF(B$1:B$200<>"",ROW(B$1:B$200))))),"")

entering it with Ctrl-Shift-Enter. (increase the 200 if you have more than 200 rows of data).

and copy down to match your list of unique values, then across for  8 columns. Copy all the formulas, paste as values to remove the links, then delete columns A:J.