logo
down
shadow

Restructure name in excel from (Last, First) to (First Last)


Restructure name in excel from (Last, First) to (First Last)

Content Index :

Restructure name in excel from (Last, First) to (First Last)
Tag : excel , By : Debashree
Date : November 29 2020, 04:01 AM

I think the issue was by ths following , Don't bother with a formula, use Flash Fill. If you really need a formula = =MID(A1&" "&A1,FIND(", ",A1)+2,LEN(A1)-1).

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Need to restructure data received from web into excel


Tag : excel , By : Brianna
Date : March 29 2020, 07:55 AM
around this issue I have a excel macro that searches many values in a column and puts the data into another worksheet. However the data is scattered and need to arrange it. , To answer your latest updated question
    Sub Search2()
    Dim i As Integer, n As Integer, SearchString As String
    Dim shFinal As Worksheet, shQuery As Worksheet, shAllData As Worksheet
    Dim iRet As Integer
    Dim strPrompt As String
    Dim strTitle As String
    Dim m As Long, p As Long, q As Long, r As Long, s As Long
    Dim range As range
    Dim vSrc As Variant, vDest() As Variant
    Dim r1 As range

    strPrompt = "Hit OK when you wish to proceed to the next search item."
    strTitle = "Next Search"

    Set shFinal = Sheets("FINAL")
    Set shQuery = Sheets("Query")
    Set shAllData = Sheets("AllData")

    n = shFinal.range("C2").End(xlDown).Row
    q = 1

    For i = 2 To n
        SearchString = shFinal.Cells(i, 3).Value
        Set qt = shQuery.QueryTables.Add(Connection:= _
            "URL;https://www.***.com/catalog/***.hsm?ItemNumber=" & SearchString _
            , Destination:=Worksheets("Query").range("A1"))

        With qt
            .Name = SearchString
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = False
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlOverwriteCells
            .SavePassword = True
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebTables = "1"
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False

            On Error Resume Next '<-- Added line

            .Refresh BackgroundQuery:=False
        End With

        If Err = 0 Then '<-- Added line

            On Error Goto 0 '<-- Added line

            shAllData.Cells(q, 1) = SearchString

            p = 1
            Do While p < 30

            If shQuery.Cells(p, 4) Like "Replaces:*" Then shAllData.Cells(q, p) = shQuery.Cells(p, 5)

            If shQuery.Cells(p, 4) Like "Crossed From:*" Then shAllData.Cells(q, p) = shQuery.Cells(p, 5)

            If shQuery.Cells(p, 4) Like "Crosses To:*" Then shAllData.Cells(q, p) = shQuery.Cells(p, 5)

            Set range = shQuery.range("E2:E25")

            For Each cell In range

                If IsEmpty(cell) Then
                    Exit For
                Else
                    r = p + 1
                    shAllData.Cells(q, r) = shQuery.Cells(r, 5)
                End If
            Next

            p = p + 1
            Loop

            iRet = MsgBox(strPrompt, vbYesNo, strTitle)
            If iRet = vbNo Then
                shQuery.UsedRange.ClearContents
                End
            Else
                shQuery.UsedRange.ClearContents
            End If


            q = q + 1

        End If  '<-- Added line

        Err = 0 '<-- Added line
    Next i


End Sub

How to split and restructure cells using excel VBA


Tag : excel , By : platformNomad
Date : March 29 2020, 07:55 AM
To fix the issue you can do call ResizeToFit macro at the end of your code
Add ResizeToFit right before End Sub in your current code
...
Next lRow
ResizeToFit ' or Call ResizeToFit
End Sub
...
Sub ResizeToFit()
Application.ScreenUpdating = False

    Dim i As Long
    For i = Range("D" & Rows.Count).End(xlUp).Row To 1 Step -1
        If IsEmpty(Range("D" & i)) Then
            Rows(i & ":" & i).Delete
        Else
            Range("E" & i) = Split(Range("D" & i), Chr(32))(1)
            Range("D" & i) = Split(Range("D" & i), Chr(32))(0)
        End If
    Next i

    For i = 1 To 5
        If i <> 4 Then
            Cells(1, i).Resize(Range("D" & Rows.Count).End(xlUp).Row, 1).Value = Cells(1, i)
        End If
    Next

Application.ScreenUpdating = True
End Sub

Restructure XML in PHP


Tag : php , By : Val
Date : March 29 2020, 07:55 AM
will help you Okay, I think the key problem here is that assigning the images to $images doesn't work like you'd expect. While such an assignment of a primitive makes a copy, assigning an object makes a reference to the same object (similar to a pointer you've worked with lower level languages). They do this because an object can be larger than a primitive, and unnecessary duplication of that is just wasteful.
However, in this case, the result is that when you unset the images from the main SimpleXML object, you're also destroying the instance in $images. Fortunately, there is a solution. You can clone the object, rather than assigning it. See http://www.php.net/manual/en/language.oop5.cloning.php
<?php
//$xml = simplexml_load_file('data.xml');
$xml = simplexml_load_string('<items>
  <item>
    <images>
      <image>A</image>
      <image>B</image>
      <image>C</image>
    </images>
  </item>
</items>');

// Loop over items in original xml
foreach ($xml->item as $item) {
    if (count($item->images->image) > 1) {
        // Clone entry
        $images = clone $item->images;

        // Remove entry and replace with empty
        unset($item->images);
        $item->addChild('images');

        for ($i = 0; $i < count($images->image); $i++) {
            $num = ($i > 0) ? $i : '';
            $item->images->addChild('image'.$num, $images->image[$i]);
        }
    }
}

//$xml->asXML('POTO.xml');
echo $xml->asXML();

How can I restructure a JS object?


Tag : javascript , By : Luciano Campos
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , I have a list of appointments structured like so (taken from JSON format): , You can use reduce -
var newData = oData.data.d.results.reduce(function(prev,curr){
    if (!prev[curr.Room]) { // if room not already in the object add it.
        prev[curr.Room] = [];
    }
    prev[curr.Room].push(curr); 
    return prev;
}, {}); // init with an empty object

console.log(newData);

Restructure column to row in Excel


Tag : excel , By : Eniac
Date : March 29 2020, 07:55 AM
it helps some times As far as I understand you want the following:
Column M as the header.
            A              B             C              D
1                       Benzen      EthyBenzen       m*p-Xxen
2    835-2017-5623
3    835-2017-5624
4    835-2017-5625
=ISERROR(VLOOKUP($A2,IF($M$1:$M$10000=B$1,$B$1:$N$10000,""),13,FALSE),"")
            A              B             C              D
1                       Benzen      EthyBenzen       m*p-Xxen
2    835-2017-5623      >0,1
3    835-2017-5624                    >0,1
4    835-2017-5625                                      >0,1
Related Posts Related QUESTIONS :
  • Formula in Excel: Matching Year and Date
  • Remove Duplicates using vba
  • How to Write Select Query and Fetch Details to Excel |VBA|
  • bold cell based on specific value in column J
  • VBA - How to merge 2 columns while copying some data from the original row
  • Pull All Rows that Contain Same Invoice Number if Cell contains value
  • How can align text to the right using VBA, if a cell contains a specific value?
  • ActiveCell recognition below 2nd row
  • How to convert FILETIME to Date in VBA?
  • VLOOKUP can't return value more than once on a row
  • How to Open & Close multiple excel workbooks based on a range of cells
  • Excel Insists that my OpenXml file has errors
  • How to deselect only one particular item in a slicer
  • How to conditionally format cells which contain the same string of text?
  • How to get the cell position/row of a search result
  • Cannot use an If-statement with multiple conditions (AND/OR) when looping through variant array?
  • Data Validation based off multiple criteria
  • How can I list horizontal data vertically without using transpose?
  • Split full name into lastname,firstname if lastname has multiple parts (i.e. van, de)
  • can't get past block if
  • Data Validation in Userform to not allow / \ : * ? " < > | for textbox
  • How to highlight duplicates in column that are not blanks?
  • How to use vlookup in vba-excel?
  • VBA using Excel version of Find on a Word Object?
  • How to delete Excel rows containing cells with less than specific amount of characters in them?
  • How to load a PQ function from a text file
  • Fill many cells in a row with one value
  • What would be the correct way to convert a column's text into multiple columns?
  • Use a Dynamic Range in an Excel Formula
  • Finding Max/Min Without Using Array
  • autofilter erroring out
  • Fixing "Unknown runtime error" code 800A03EC in VBScript to run macro without opening Excel
  • How to access the value of a string when defining within a loop
  • How to keep formulas working when copy and pasting in Excel?
  • How to calculate the difference between max/min dates on non static pivot tables?
  • How to store row and column of excel into array (more than 1 column and 1 row)
  • Can having multiple instances of Excel open cause VBA issues?
  • Why vba is replacing SN-Sh as zeros in the Word Snapshot as 00ap00ot?
  • Excel formula to return value of cell in column A based off the relative cell value in column B
  • how to remove negative sign from a characters or text?
  • How to get the border sizes of a userform?
  • Allow two cells to depend on each other
  • Excel count rows while treating merged rows as one
  • Search for a text segment using MAX IF in Excel
  • Unable to set object VBA
  • How can I transfer to a sheet if range says "Fail" in any cell?
  • Shorten a cell, nested Ifs Excel
  • Finding the volume of a tank in VBA given the radius, height, and depth
  • Compile Error on simple VBA form and command button click code
  • Looping through Workbooks and Copy a Dynamic range to Master Workbook
  • Using .NET HashTable Return Type in VBA
  • How do I not get Run-time error '13': Type mismatch while iterating rows?
  • How to change image of a Powerpoint shape?
  • Adding character before text in Excel without using formula
  • How can I create a list of numbers that is like the following?
  • How to fix " Run-time error '380' in Excel VBA?
  • How can you repeat a number x times in a column in a continuation, in excel
  • How to delete the content of a txt file with vba?
  • How to work with more Target.Address (range)
  • Change arrow color based on its label or value
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com