PDA

View Full Version : Compare cells with one value against cells with two values then merge and count



JDS_916
07-27-2014, 04:40 PM
Hello to everyone,

I have exported sharepoint list data to Excel to produce a number of pivot tables and charts. The list has three columns (‘country’, ‘article’ and ‘finding’). The ‘article’ column is a choice column configured for multiple selections, so two or more values are shown in Excel separated by a semi colon and a hash (;#). For example:

Portugal Article 1 shortcoming
Spain Article 2 shortcoming
Italy Article 3;#Article 8 shortcoming
Poland Article 4 observation
UK Article 2 observation
France Article 25 observation
Germany Article 1;#Article 4 shortcoming
Sweden Article 3;#Article 1 observation
Denmark Article 1 shortcoming
Norway Article 1 shortcoming

When this data is shown in my clustered column chart, it correctly gives the axis fields/categories as Article 1 through 25. The chart’s values are represented as findings. The particular chart shows the number of findings against a particular article. So, with the example data above, the chart would show the following:

Article 1 with 3 findings
Article 2 with 2 findings
Article 3;#Article 8 with 1 finding
Article 4 with 1 finding
Article 1;#Article 4 with 1 finding
Article 3;#Article 1 with 1 finding
Article 25 with 1 finding

However, although I want this chart for certain statistics, I also want a true count of individual articles. As you can see, article 1 really has 4 findings not 3, but as one article 1 is associated with article 4, it does not get counted. This goes for article 3 also, which really has 2 findings but would not be displayed as an individual column because they are associated with other articles.

Is there any way that Excel, either through a formula or VBA, can show the chart with individual article counts. My problem is the multiple values.

Regards,

JDS_916

Bob Phillips
07-28-2014, 12:15 AM
Public Sub Reformat()
Dim articles As Variant
Dim num As Long
Dim lastrow As Long
Dim i As Long

With ActiveSheet

.Columns(2).Replace What:="#", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row

For i = lastrow To 2 Step -1

If InStr(.Cells(i, "B").Value, ";") > 0 Then

num = Len(.Cells(i, "B").Value) - Len(Replace(.Cells(i, "B").Value, ";", ""))
articles = Split(.Cells(i, "B").Value, ";")
.Rows(i + 1).Resize(num).Insert
.Cells(i, "A").Resize(, 3).Copy .Cells(i + 1, "A").Resize(num)
.Cells(i, "B").Resize(num + 1).Value = Application.Transpose(articles)
End If
Next i
End With
End Sub

JDS_916
07-28-2014, 12:06 PM
Hi xld,

Amazing, works perfect. Many thanks for such assistance.

Could you please tell me how to sort the Articles, i.e. Article 1, Article 2, Article 3, etc. Sorting A to Z or Z to A does not work.

Regards,

JDS_916

Bob Phillips
07-28-2014, 03:14 PM
What do you mean it doesn't work, sort works?

JDS_916
07-29-2014, 11:40 AM
Everything works fine, but after the macro runs I would like to show the axis fields in article order, i.e article 1, article 2, article 3, article 4, article 5 through to article 25. However, the column is sorted as follows:



Article 1


Article 10


Article 11


Article 12


Article 13


Article 14


Article 15


Article 16


Article 17


Article 18


Article 19


Article 2


Article 20


Article 3


Article 4


Article 5


Article 6


Article 7


Article 8.1


Article 8.2


Article 8.3


Article 9






Do I need to set up a custom list?

Bob Phillips
07-29-2014, 03:45 PM
A custom list would have a lot of values, probably more than it can handle. Could the text be changed to a standard format, such as Article 001, Article 010, Article 008.1, etc.?

JDS_916
07-30-2014, 11:57 AM
Unfortunately the text must stand as is. I tried a custom list until it reached 255 characters. This gave me Article 1 through Article 18 and it seems to work.