Copy a Cell content if a column contains a specific word.

Thomanji

New Member
Joined
May 29, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi Everyone,

I am struggling with a formula to accomplish a task to concert a vertical list to a horizontal database type list.
I would be very happy if I could get some pointers on where I go wrong and how to archive my goal.

Here is the problem. I got a list like below, Cell A has a label which repeats every 11 cells. Starting with Name and End with Time Zone.

Name Text Doe1
Email me1@you.com
Operator Someone1
Product/Service name of product1
Phone 123 1234567
Company ABC Company1
Referrer URL Google
Search Engine Google
IP 210.186.133.177
Country/Region Malaysia
State Kuala Lumpur
City Kuala Lumpur[Client Info]
Language en-GB
Time Zone GMT +08
Name Text Doe2
Email me2@you.com
Operator Someone2
Product/Service name of product2
Phone 123 1234567
Company ABC Company2
Referrer URL Google
Search Engine Google
IP 210.186.133.177
Country/Region Malaysia
State Kuala Lumpur
City
Language en-GB
Time Zone GMT +08
Name Text Doe3
Email me3@you.com
Operator Someone3
Product/Service name of product3
Phone 123 1234567
Company ABC Company3
Referrer URL Google
Search Engine Google
IP
Country/Region Malaysia
State Kuala Lumpur
City Kuala Lumpur[Client Info]
Language en-GB
Time Zone GMT +08

I am trying to convert this to a horizontal list with one cell per data block.

So I made a list with the horizontal values like:
Name Email Operator Product/Service Phone Company Referrer URL Search Engine Google IP Country/Region State City Language Time Zone

Than the idea is to look up Cell A and if it contains "Name" it should copy the content of Cell A2 to the cell under the name heading.
The goal is to have each vertical value under the appropriate label so that in the end this can be a CSV list of the entries.

I tried something like this for example in name
=IF(ISNUMBER(SEARCH("Name",A:A)),B:B,"") this does not work
then I did this
=IF(ISNUMBER(SEARCH("Name",A1)),B1,"")
it works but has always 11 empty cells.

=IF(ISNUMBER(SEARCH("Email",A1:A75115)),B1:B75115,"")
this works somewhat but there are some strange results. Mainly that the operator goes in the second line.

I guess there should be a better way but my formula skills are not very sophisticated and I hope that maybe someone has a tip for a better way to do this.
I have my test file attached, I do not understand why "operator" would populate the second cell and not the first. Than each next header label is another cell down.

Best wishes,
Thom
 

Attachments

  • excel-test.jpg
    excel-test.jpg
    97.3 KB · Views: 8

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
92
Office Version
  1. 365
Platform
  1. Windows
Are you familiar with VBA programming?
If so, it can be done very easily.

Your data has 14 rows for each entry.
So create the 14 headings in row 1 (in the current worksheet or a new worksheet - your choice)
Create a loop to copy each 14 rows of data, and transpose it to the blank row under your headings.
Once completed, you will have to clean up the copied data to remove the included headings: eg. State Kuala Lumpur -> Kuala Lumpur

That's it.
 

Thomanji

New Member
Joined
May 29, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi Larry,

Thank you very much for taking the time to look at me issue and helping me out, I really appreciate it.
This is a good way, I did not thing of using a VBA. However my knowledge is even less with VBA.
However, I managed to get it to copy and transpose the first block but I do not understand how to loop and have it be done with the next 14 cells.

I have the following:

Sub Macro1()
'
' Macro1 Macro
'

'
Range("B1:B14").Select
Selection.Copy
Windows("Pasrse.xlsx").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Windows("Parsing-vertical-list.xlsx").Activate
End Sub

But I do not know how to loop this and move the range down 14 cells on the one sheet and one cell on the paste sheet.
I would be really happy for any pointer on this.
 

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
92
Office Version
  1. 365
Platform
  1. Windows
Here is the code:

VBA Code:
Option Explicit

Sub ProcessBrowsingData()
'==========================================
'   Split the browsing data into columns
'==========================================
Dim BData As Worksheet                              'placeholder for the worksheet name
Dim BlockFirstRow As Long, BlockLastRow As Long     'to define the first and last rows of each data block
Dim LastDataRow As Long                             'the last row of data
Dim PasteRow As Long                                'on which row to paste the data
Dim LastCopiedRow As Long                           'last row of the copied & transposed data
Dim RowNo As Long                                   'row counter, used when cleaning up the data

    '==========================================
    '   Identify this worksheet
    '   Makes reference to this sheet easier
    '==========================================
    Set BData = ActiveWorkbook.Sheets("BrowsingData")
    
    '================================
    '   Remove any existing result
    '================================
    BData.Columns("B:O").ClearContents
    
    '================================
    '   Create the column headings
    '================================
    With BData
        .Range("B1") = "Name Text"
        .Range("C1") = "Email"
        .Range("D1") = "Operator"
        .Range("E1") = "Product/Service"
        .Range("F1") = "Phone"
        .Range("G1") = "Company"
        .Range("H1") = "Referrer URL"
        .Range("I1") = "Search Engine"
        .Range("J1") = "IP"
        .Range("K1") = "Country/Region"
        .Range("L1") = "State"
        .Range("M1") = "City"
        .Range("N1") = "Language"
        .Range("O1") = "Time Zone"
    End With

    '==========================================
    '   Find the last data row
    '   so that we know when to stop copying
    '==========================================
    LastDataRow = BData.Range("A1048576").End(xlUp).Row
    
    '=============================================
    '   Specify the initial block and paste row
    '=============================================
    BlockFirstRow = 1   'Tell the program to begin copying from row 1
    BlockLastRow = 14   'Tells the program the size of the block
    PasteRow = 2
    
    '===================
    '   Begin copying
    '===================
    Do
        '================================
        '   Copy each block of 14 rows
        '================================
        BData.Range("A" & BlockFirstRow & ":A" & BlockLastRow).Copy
            '=================================
            '   Transpose the data
            '   from vertical to horizontal
            '=================================
            BData.Range("B" & PasteRow).PasteSpecial , , , True
    
        
        '================================
        '   Define the next data block
        '================================
        BlockFirstRow = BlockFirstRow + 14  'begining of next block of data
        BlockLastRow = BlockLastRow + 14    'ending of next block of data
        PasteRow = PasteRow + 1             'next row to paste the transposed data
        
    Loop Until BlockFirstRow >= LastDataRow
    
    '==============================================================
    '   Now clean up the data
    '   REPLACE removes the heading that is included in the data
    '   TRIM removes any leading- and training-spaces
    '==============================================================
    LastCopiedRow = BData.Range("B1048576").End(xlUp).Row
    
        '=======================
        '   Clean up the data
        '=======================
        For RowNo = 2 To LastCopiedRow
        
            BData.Range("B" & RowNo) = Trim(Replace(BData.Range("B" & RowNo), "Name Text", ""))
            BData.Range("C" & RowNo) = Trim(Replace(BData.Range("C" & RowNo), "Email", ""))
            BData.Range("D" & RowNo) = Trim(Replace(BData.Range("D" & RowNo), "Operator", ""))
            BData.Range("E" & RowNo) = Trim(Replace(BData.Range("E" & RowNo), "Product/Service", ""))
            BData.Range("F" & RowNo) = Trim(Replace(BData.Range("F" & RowNo), "Phone", ""))
            BData.Range("G" & RowNo) = Trim(Replace(BData.Range("G" & RowNo), "Company", ""))
            BData.Range("H" & RowNo) = Trim(Replace(BData.Range("H" & RowNo), "Referrer URL", ""))
            BData.Range("I" & RowNo) = Trim(Replace(BData.Range("I" & RowNo), "Search Engine", ""))
            BData.Range("J" & RowNo) = Trim(Replace(BData.Range("J" & RowNo), "IP", ""))
            BData.Range("K" & RowNo) = Trim(Replace(BData.Range("K" & RowNo), "Country/Region", ""))
            BData.Range("L" & RowNo) = Trim(Replace(BData.Range("L" & RowNo), "State", ""))
            BData.Range("M" & RowNo) = Trim(Replace(BData.Range("M" & RowNo), "City", ""))
            BData.Range("N" & RowNo) = Trim(Replace(BData.Range("N" & RowNo), "Language", ""))
            BData.Range("O" & RowNo) = Trim(Replace(BData.Range("O" & RowNo), "Time Zone", ""))
    
        Next RowNo
        
    '========================
    '   Format the columns
    '========================
    With BData
        .Columns("B:O").HorizontalAlignment = xlLeft
        .Columns("B:O").AutoFit
        .Range("B1").Select
    End With
        
        
End Sub
 

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
92
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

And here is the result:

VBA Samples.xlsm
ABCDEFGHIJKLMNO
1Name Text Doe1Name TextEmailOperatorProduct/ServicePhoneCompanyReferrer URLSearch EngineIPCountry/RegionStateCityLanguageTime Zone
2Email me1@you.comDoe1 me1@you.comSomeone1name of product1123 1234567ABC 1 GoogleGoogle210.186.133.177MalaysiaKuala LumpurKuala Lumpur[Client Info]en-GBGMT +08
3Operator Someone1Doe2 me2@you.comSomeone2name of product2123 1234567ABC 2 GoogleGoogle210.186.133.177MalaysiaKuala Lumpur en-GBGMT +08
4Product/Service name of product1Doe3 me3@you.comSomeone3name of product3123 1234567ABC 3 GoogleGoogle MalaysiaKuala LumpurKuala Lumpur[Client Info]en-GBGMT +08
5Phone 123 1234567
6Company ABC Company1
7Referrer URL Google
8Search Engine Google
9IP 210.186.133.177
10Country/Region Malaysia
11State Kuala Lumpur
12City Kuala Lumpur[Client Info]
13Language en-GB
14Time Zone GMT +08
BrowsingData
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,529
Office Version
  1. 365
Platform
  1. Windows
So I made a list with the horizontal values like:
Then you can also do this pretty easily with a formula.

Here is a smaller example (I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. ;))
Hopefully you can scale it up to match your data and layout. After adjusting the ranges, copy the formula across and down.

20 05 29.xlsm
ABCDEFG
1NameTomNameEmailOperatorCompany
2Emailtom@abc.defTomtom@abc.defSomeone1Company ABC
3OperatorSomeone1Fredfred@aaa.comAnother operatorHHH Inc
4CompanyCompany ABCJenjj@aole.net.uk Houses Plus
5NameFredAnn Thomsa_t@homeSomeone1Big Shoes Ltd
6Emailfred@aaa.com    
7OperatorAnother operator    
8CompanyHHH Inc
9NameJen
10Emailjj@aole.net.uk
11Operator
12CompanyHouses Plus
13NameAnn Thoms
14Emaila_t@home
15OperatorSomeone1
16CompanyBig Shoes Ltd
17
Vertical to Horizontal
Cell Formulas
RangeFormula
D2:G7D2=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$1:$B$200)/($A$1:$A$200=D$1),ROWS(D$2:D2)))&"","")
 

Thomanji

New Member
Joined
May 29, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Larry Haydn - Wow, you are awesome. This is great, thank you very much for this.
Peter_SSs - thanks this is working too

I thank you both for your support on this. I am appreciating this assistance very much. Its nice to see that in the community we support each other.
I will pay it forward ;)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,529
Office Version
  1. 365
Platform
  1. Windows
You're welcome. :)

If your data is at all large and you are considering going with a macro, then this should be very fast. It assumes ..
- original data in columns A:B starting at row 1 as per your image from post #1.
- every 'block' of data has the same labels in the same order in column A.
- nothing is in column D and columns to the right

The code will automatically determine the headings required and enter them in D1, E1, F1, ...

VBA Code:
Sub VerticalToHorizontal()
  Dim a As Variant, b As Variant
  Dim RowsPerBlock As Long, NumBlocks As Long, i As Long, j As Long, BaseNum As Long
  
  RowsPerBlock = Columns(1).Find(What:=Range("A1").Value, LookAt:=xlWhole).Row - 1
  a = Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).Value
  NumBlocks = UBound(a) / RowsPerBlock
  ReDim b(1 To NumBlocks, 1 To RowsPerBlock)
  Do Until i = NumBlocks
    i = i + 1
    BaseNum = (i - 1) * RowsPerBlock
    For j = 1 To RowsPerBlock
      b(i, j) = a(BaseNum + j, 1)
    Next j
  Loop
  With Range("D1").Resize(, RowsPerBlock)
    .Value = Application.Transpose(Range("A1").Resize(RowsPerBlock).Value)
    .Offset(1).Resize(NumBlocks).Value = b
  End With
End Sub
 

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
92
Office Version
  1. 365
Platform
  1. Windows
Larry Haydn - Wow, you are awesome. This is great, thank you very much for this.
Peter_SSs - thanks this is working too

I thank you both for your support on this. I am appreciating this assistance very much. Its nice to see that in the community we support each other.
I will pay it forward ;)

Many years ago (2003~2004?) I was doing an online store project with also a physical store.
The application requires integrating a barcode scanner with my application. And I have no idea how to do that.
I posted a question on some forums, and got the answer as well as the code in less than 15 minutes.

So here, I am giving back.
I hope one day you will continue this fine tradition too :)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,236
Messages
5,576,897
Members
412,752
Latest member
LUIS SAMANO
Top