automatically insert group header as the group changes in the same row

Victtor

Board Regular
Joined
Jan 4, 2007
Messages
170
Office Version
  1. 365
Platform
  1. Windows
for the purpose of this spreadsheet, I have created some arbitrary groups:

these are located in column a.
cars
fast food
video games
beans

In column X, is a database of items that include these:
taurus
xj6
altima
sebring
taco bell
popeyes
mcdonalds
captain d's
halo
civilization
words with friends
donkey kong
pinto
blackeye peas
lima
northern

column W identifies what heading each item goes under (i.e. video games, beans, etc)

In column c, as the result of a formula, are these items again:
taurus
xj6
altima
sebring
taco bell
popeyes
mcdonalds
captain d's
halo
civilization
words with friends
donkey kong
pinto
blackeye peas
lima
northern



I need a formula that will automatically insert a group heading ABOVE the group in column C. In this example there are 4 items in each category. There could any number of items in any category. If there are no items for that category, that category is omitted. the categories always appear in this order (i.e. all fast food items, if any, will always come before any video game items)

here is an example of the ideal result in column C: <b></b>

<b>cars</b>
gremlin
xj6
altima
sebring
<b>fast food</b>
taco bell
popeyes
mcdonalds
captain d's
<b>video games</b>
halo
civilization
words with friends
donkey kong
<b>beans</b>
pinto
blackeye peas
lima
northern

can anyone help me?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
I'm assuming the data values quoted start in row 2, with headings in row 1.

This would be much easier using a macro.

Rich (BB code):
Sub Make_List()
  Dim a, b
  Dim i As Long, k As Long
  
  a = Range("W1", Range("X" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a, 1) * 2, 1 To 1)
  For i = 2 To UBound(a, 1)
    If a(i, 1) <> a(i - 1, 1) Then
      k = k + 1
      b(k, 1) = a(i, 1)
    End If
    k = k + 1
    b(k, 1) = a(i, 2)
  Next i
  Range("C2").Resize(k).Value = b
End Sub


Here is a suggested formula solution. Perhaps you'll understand why I suggested a macro would be simpler. ;)
Perhaps somebody will come up with a better/shorter one than this but also note this is not robust. For example, if say a fast food item was "beans" (one of the other category names) the formula list will give incorrect results.
Anyway, if you want to give it a try, and you are using Excel 2010 or later, the formula in C3 is copied down.

Excel Workbook
ABCWX
1Header AHeader CHeader WHeader X
2carscarscarstaurus
3fast foodtauruscarsxj6
4video gamesxj6carsaltima
5beansaltimacarssebring
6sebringfast foodtaco bell
7fast foodfast foodpopeyes
8taco bellfast foodmcdonalds
9popeyesfast foodcaptain d's
10mcdonaldsvideo gameshalo
11captain d'svideo gamescivilization
12video gamesvideo gameswords with friends
13halovideo gamesdonkey kong
14civilizationbeanspinto
15words with friendsbeansblackeye peas
16donkey kongbeanslima
17beansbeansnorthern
18pinto
19blackeye peas
20lima
21northern
22
List
 
Upvote 0
Peter, thanks for responding. I am choosing the formula method because no all my users will be able to run macros

The formula in C3 is producing an error (with the "" result). I am not sure why. I simply copied and pasted this formula.

Any help would be appreciated
 
Upvote 0
The formula in C3 is producing an error (with the "" result). I am not sure why. I simply copied and pasted this formula.
Then you most likely have an error with the ranges in the formula.
Assuming the layout is basically as I have it ..
- everywhere in that formula where there is A$5 you need to change that 5 to (at least) the last row of data in column A.
- everywhere there is $17 you need to change that 17 to (at least) the last row of data in columns W & X.
 
Upvote 0
Peter, I have transferred the formula to my project. however, the headings are not showing up. I am not sure why. Also, you hard coded C2 in the example formula, does it need to be hard coded?




=IFERROR(IF(ROW()-LOOKUP(ROW(),ROW(B$34:B36)/ISNUMBER(MATCH(B$34:B36,K$20:K$26,0)))<=COUNTIF(N$3:N$200,LOOKUP(ROW(),ROW(B$34:B36)/ISNUMBER(MATCH(B$34:B36,K$20:K$26,0)),B$34:B36)),INDEX(W$36:W$71,AGGREGATE(15,6,(ROW(W$36:W$71)-ROW(W$36)+1)/(N$3:N$200=LOOKUP(ROW(),ROW(B$34:B36)/ISNUMBER(MATCH(B$34:B36,K$20:K$26,0)),B$34:B36)),ROW()-LOOKUP(ROW(),ROW(B$34:B36)/ISNUMBER(MATCH(B$34:B36,K$20:K$26,0))))),INDEX(N$3:N$200,AGGREGATE(14,6,(ROW(N$3:N$200)-ROW(N$2)+1)/(N$3:N$200=LOOKUP(ROW(),ROW(B$34:B36)/ISNUMBER(MATCH(B$34:B36,K$20:K$26,0)),B$34:B36)),1)+1)),"")
 
Upvote 0
Peter, I have transferred the formula to my project. however, the headings are not showing up. I am not sure why.
Please explain clearly your current sheet layout and what cell contains the first heading by formula (cell C2 in post #2) and what cell contains the first of these long formulas (cell C3 previously).
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top