Appendix Thread. App Index Rws() Clms() Majic code line Codings for other Threads, Tables etc.)

**DocAElstein** · 05-07-2020, 01:27 PM

In support of these Post
http://www.eileenslounge.com/viewtopic.php?f=30&t=34629

These are all text Files. The macro in this post ( https://excelfox.com/forum/showthrea...ll=1#post13248 ) will make them
XXXXSeperatedValuesTextFiles.JPG : https://imgur.com/A2IebLK
PipeSeperatedValuesTextFiles.JPG : https://imgur.com/Y9676cg

Comma Seperated values
(Sometimes called English Comma Seperated Values )

zyxw123

,

jhas

,

rider

,

roger

,

anjus

,

sumanjjj

Leonardo

,

umpsbug

,

kinjals

,

tinamishra

,

kinjal124

,

WigWam

Share ‘CommaSeperatedValues.txt’ : https://app.box.com/s/qcjpeu0vt875513gqawmtoufeba3xb28
Share ‘CommaSeperatedValues.csv’ : https://app.box.com/s/w2barpwasveltam4lutjwijks0zft0vq

Pipe Seperated Text Files

zyxw123

|

jhas

|

rider

|

roger

|

anjus

|

sumanjjj

Leonardo

|

umpsbug

|

kinjals

|

tinamishra

|

kinjal124

|

WigWam

Share ‘PipeSeperatedValues.txt’ : https://app.box.com/s/47eo2pmeqlmnjj5h9hlxog8ts47nlgj7
Share ‘PipeSeperatedValues.csv’ : https://app.box.com/s/o7zculmorhyys3r9b6hwwuc3wry1mr6p

**DocAElstein** · 05-07-2020, 02:05 PM

In support of this Post
https://excelfox.com/forum/showthrea...ll=1#post13246

Run the macro below, Sub XXXXXSeperatedValuesTextFiles() ,
It will make text files in the same folder as the folder in which the macro is run in.
( The macro is also in the shared File, XXXXXSeperatedValues.xlsm )
XXXXSeperatedValuesTextFiles.JPG : https://imgur.com/A2IebLK

The text files are shown in the last post https://excelfox.com/forum/showthrea...ll=1#post13247

Code:

Option Explicit
Sub XXXXXSeperatedValuesTextFiles()     '                      https://excelfox.com/forum/showthread.php/2467-COPY-AND-PASTE?p=13246#post13246
 Call Make____SeperatedValuesTextFiles("CommaSeperatedValues", ",")                        ' make  CSV files ( Comma Seperated Values Files )
 Call Make____SeperatedValuesTextFiles("TabSeperatedValues", vbTab)                        ' make  Tab Seperated Values Files
 Call Make____SeperatedValuesTextFiles("NMODSeperatedValues", "NMOD")                      ' make  NMOD Seperated Values Files
 Call Make____SeperatedValuesTextFiles("SemiColonSeperatedValues", ";")                    ' make  ; Seperated Values Files ( sometimes called german Comma seperated files )
 Call Make____SeperatedValuesTextFiles("GollyWobblesSeperatedValues", "GollyWobbles")      ' make  GollyWobbles Seperated Values Files
 Call Make____SeperatedValuesTextFiles("PipeSeperatedValues", "|")                     ' make  Pipe Seperated Values Files
End Sub

Sub Make____SeperatedValuesTextFiles(ByVal Filname As String, Seprator As String)
' Make long string for text file
Dim strTotalFile As String
 Let strTotalFile = MakeA____SeperatedValuesTextFile(Seprator)
' .txt  Text File
Dim Highway1 As Long: Let Highway1 = FreeFile(0) 'range 1 – 255, inclusive - next free
 Open ThisWorkbook.Path & "\" & Filname & ".txt" For Append As #Highway1 ' Will be made if not there
 Print #Highway1, strTotalFile
 Close #Highway1

' .csv  Text File
Dim Highway2 As Long: Let Highway2 = FreeFile(0) 'range 1 – 255, inclusive - next free
 Open ThisWorkbook.Path & "\" & Filname & ".csv" For Append As #Highway2 ' Will be made if not there
 Print #Highway2, strTotalFile
 Close #Highway2

End Sub
Function MakeA____SeperatedValuesTextFile(ByVal Seprator As String) As String
Rem 1 Rows
Dim AvinashNamesRow1() As Variant, AvinashNamesRow2() As Variant
 Let AvinashNamesRow1() = Array("zyxw123", "jhas", "", "rider", "roger", "anjus", "sumanjjj")
 Let AvinashNamesRow2() = Array("Leonardo", "umpsbug", "kinjals", "", "tinamishra", "kinjal124", "fxe632")
Rem 2 make single string for text files
Dim strOut As String
 Let strOut = Join(AvinashNamesRow1(), Seprator) & vbCr & vbLf & Join(AvinashNamesRow2(), Seprator) & vbCr & vbLf
 Let MakeA____SeperatedValuesTextFile = strOut
End Function

Ref
https://excelfox.com/forum/showthrea...-a-range/page3

XXXXXSeperatedValues.xlsm : https://app.box.com/s/jvlu048tkg0rjw7xi4c4r838abw1z7bi

**sandy666** · 05-07-2020, 02:21 PM

Originally Posted by DocAElstein

ADHahdhdh

do ya have : Attention deficit hyperactivity disorder (ADHD) ???

**DocAElstein** · 05-07-2020, 02:29 PM

Originally Posted by sandy666

do ya have : Attention deficit hyperactivity disorder (ADHD) ???

Probably

it is fixer's fault - he is driving me mad!

**sandy666** · 05-07-2020, 02:34 PM

easy, easy, this is a patience test

**DocAElstein** · 05-07-2020, 02:47 PM

It is character building.
Actually, you are good at geussing what he wants...
I will post just once more now in the main Thread , and then go and break some more rocks for relaxation for the rest of the day..
C ya tomorrrow

**DocAElstein** · 05-08-2020, 12:57 PM

Another attempt to geuss what fixer is askig for from here:
https://excelfox.com/forum/showthrea...ll=1#post13256

Code:

Sub OpenTxtFiles_ValuesToBeSeperatedIntoExcelCells()
' Comma seperated values text files
Call OpenA____SeperatedValuesTextFile("CommaSeperatedValues.csv", ",")
Call OpenA____SeperatedValuesTextFile("CommaSeperatedValues.txt", ",")
End Sub

Sub OpenA____SeperatedValuesTextFile(ByVal Filname As String, ByVal Seprator As String)
Rem 1 Get text file as long string.
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = ThisWorkbook.Path & "\" & Filname '
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
TotalFile = Space(LOF(FileNum)) '....and wot recives it hs to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
Rem 2 Put values in Excel
Dim Ws1 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets.Item(1)
 Ws1.Cells.ClearContents
'2b) Split Total File text into  a 1 Dimensional array into rows
Dim RwTxt() As String: Let RwTxt() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare)
Dim Clms() As String
 Let Clms() = Split(RwTxt(0), Seprator, -1, vbBinaryCompare) '  This will be the first row of data. Here we are doing it just to gat the column count. In the loop below, we will use it for every row, including initially this first row. We need it below to allow us to access each value seperately seperated via the seprator, seprator
Dim HedClmsCnt As Long: Let HedClmsCnt = UBound(Clms) + 1    '  +1 is required , as , by default , a 1Dimensional array from split has first element indicie of 0 , so Ubound will be 1 less than the number of elements
Dim arrOut() As String                                       '  I must make this dynamic, since i must use the TReDim method to size it. This is because the Dim statement will not accept variables or non static values: It omly accepts actual integer hard coded numbers
ReDim arrOut(1 To UBound(RwTxt) + 1, 1 To HedClmsCnt)        '  +1 is required , as , by default , a 1Dimensional array from split has first element indicie of 0 , so Ubound will be 1 less than the number of elements
Dim RwCnt As Long
    For RwCnt = 0 To UBound(RwTxt)
    '2c) Split each row into seperated values
     Let Clms() = Split(RwTxt(RwCnt), Seprator, -1, vbBinaryCompare)
    Dim ClmCnt As Long
        If Not UBound(Clms()) = -1 Then '   This might be the case fo extra rows in the text file with no  seperators in s
            For ClmCnt = 1 To HedClmsCnt
             Let arrOut(RwCnt + 1, ClmCnt) = Clms(ClmCnt - 1)
            Next ClmCnt
           Else
           End If
    Next RwCnt
Rem 2d) Put values from text file into first worksheet
Ws1.Range("A1").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)) = arrOut()
End Sub

**DocAElstein** · 05-08-2020, 12:57 PM

Try number 12976436. Education in Text files

In support of this Thread: http://www.eileenslounge.com/viewtopic.php?f=30&t=34629
DF.txt
Text file, DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic
Original uploaded DF.txt looked like this as seen for example using a Text Editor. ( Notepad is just one of many available text editors )
10,18052020,9.23,0015378
20,1018GS2026,GS,IN0020010081,0.00,0.00,10.00,0.00 ,0.00,10.00
20,1025GS2021,GS,IN0020010040,0.00,0.00 ……..etc.
You are using a comma in DF.txt to separate the values. Because you are using a comma to separate your values , we sometimes call such a file a comma separated values file., and we often give a comma separated values text file the extension .csv. But you don’t have to. It’s is your choice. Both DF.txt or DF.csv is OK. You can use either for your text file.
You have used DF.txt for your comma separated values text file. That is a bit unusual, but it is OK. Its your choice.

This macro will allow us to examine that text file, ( for simplicity I am using a test file example of just 3 rows )

Code:

Sub WotsInDF_Text()  '  '  http://www.eileenslounge.com/viewtopic.php?p=268809#p268809   What is in   DF.txt :  https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic           https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13273&viewfull=1#post13273
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = ThisWorkbook.Path & "\csv Text file Chaos\" & "DF_first 3 rows.txt" ' From vixer zyxw1234  : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629     DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
TotalFile = Space(LOF(FileNum)) '....and wot recives it has to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
' What the fuck is in this string?
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) '    https://excelfox.com/forum/showthread.php/2302-quot-What%e2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string?p=11016&viewfull=1#post11016     https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11818&viewfull=1#post11818

End Sub

here is the full single string of the text file, shown in two forms:
_ as seen in a text editor
_ in a VBA code line form

Code:

10,18052020,9.23,001537820,1018GS2026,GS,IN0020010081,0.00,0.00,10.00,0.00,0.00,10.0020,1025GS2021,GS,IN0020010040,0.00,0.00,10.00,0.00,0.00,10.00
"10" & Chr(44) & "18052020" & Chr(44) & "9" & "." & "23" & Chr(44) & "0015378" & vbCr & vbLf & "20" & Chr(44) & "1018GS2026" & Chr(44) & "GS" & Chr(44) & "IN0020010081" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & vbCr & vbLf & "20" & Chr(44) & "1025GS2021" & Chr(44) & "GS" & Chr(44) & "IN0020010040" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00"

here the same again, just shown slightly differently for easy of explanation

Code:

"10" & Chr(44) & "18052020" & Chr(44) & "9" & "." & "23" & Chr(44) & "0015378" & vbCr & vbLf
 & "20" & Chr(44) & "1018GS2026" & Chr(44) & "GS" & Chr(44) & "IN0020010081" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & vbCr & vbLf 
& "20" & Chr(44) & "1025GS2021" & Chr(44) & "GS" & Chr(44) & "IN0020010040" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00"

we see the value seperator comma , ( character 44 ) and the line seperator, vbCr & vbLf

In support of this Thread: https://excelfox.com/forum/showthrea...3427#post13427
Alert 24 Mai..csv Alert 24 MaiDotDotcsv.jpg : https://imgur.com/0HsAOLj

We analyse using the same macro as above, with this changed code line

Code:

 Let PathAndFileName = ThisWorkbook.Path & "\csv Text file Chaos\" & "Alert 24 Mai..csv" '  https://excelfox.com/forum/showthread.php/2500-Conditionally-delete-entire-row-with-calculation-within-files     Share ‘Alert 24 Mai..csv’  :  https://app.box.com/s/599q2it3uck3hfwm5kscmmgtn0be66wt

Here is the results

Code:

NSE,236,6,>,431555,A,,,,,GTTNSE,25,6,>,431555,A,,,,,GTTNSE,15083,6,>,431555,A,,,,,GTTNSE,17388,6,>,431555,A,,,,,GTTNSE,100,6,>,431555,A,,,,,GTTNSE,22,6,>,431555,A,,,,,GTT,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Entire row of row 3 & row 4 both will be deleted after runing the macro,,,,,,

Code:

"NSE" & Chr(44) & "236" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "25" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "15083" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "17388" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "100" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "22" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vb
Lf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "Entire" & " " & "row" & " " & "of" & " " & "row" & " " & "3" & " " & "&" & " " & "row" & " " & "4" & " " & "both" & " " & "will" & " " & "be" & " " & "deleted" & " " & "
after" & " " & "runing" & " " & "the" & " " & "macro" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf

Here again adjusted for clarity

Code:

"NSE" & Chr(44) & "236" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf &
 "NSE" & Chr(44) & "25" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & 
"NSE" & Chr(44) & "15083" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & 
"NSE" & Chr(44) & "17388" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & 
"NSE" & Chr(44) & "100" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & 
"NSE" & Chr(44) & "22" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf &
 Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & 
Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & 
Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & 
Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & 
Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & 
Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & 
Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & 
Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "Entire" & " " & "row" & " " & "of" & " " & "row" & " " & "3" & " " & "&" & " " & "row" & " " & "4" & " " & "both" & " " & "will" & " " & "be" & " " & "deleted" & " " & "after" & " " & "runing" & " " & "the" & " " & "macro" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf

.csv text file is using commas , for the value separator, and for the line separate it has the typical convention of vbCr & vbLf

**DocAElstein** · 05-08-2020, 12:57 PM

In Support of this forum question
https://eileenslounge.com/viewtopic....268481#p268481

_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	E	F	G	H	I	J	L	N
6				#VALUE!		Got missing number in column E	CSE equivalent	CSE equivalent
7	Title 5	Title 6	Title 7	Title 8	Title 9	Title 10
8			eileenslounge	1000.00			1000	Got one or more missing numbers
9		1	eileenslounge1	4.00			4	Got missing number in column E
10	1	2	eileenslounge2	9.00			9
11	2	3	Others	16.00			16
12	3	4	eileenslounge	1000.00			1000
13	4	5	eileenslounge1	36.00			36
14	5	6	eileenslounge2	49.00			49
15	6	7	Others	64.00			64
16	7	8
17	8

Worksheet: data

_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	E	F	G	H	I	J	L	N
6				*=IF(G6="eileenslounge",1000,F7E8)**		=IF(F7="","Got one or more missing numbers",IF(E8="","Got missing number in column E",""))	CSE equivalent	CSE equivalent
7	Title 5	Title 6	Title 7	Title 8	Title 9	Title 10
8			eileenslounge	*=IF(G8="eileenslounge",1000,F9E10)**			*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
9		1	eileenslounge1	*=IF(G9="eileenslounge",1000,F10E11)**			*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
10	1	2	eileenslounge2	*=IF(G10="eileenslounge",1000,F11E12)**			*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
11	2	3	Others	*=IF(G11="eileenslounge",1000,F12E13)**			*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
12	3	4	eileenslounge	*=IF(G12="eileenslounge",1000,F13E14)**			*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
13	4	5	eileenslounge1	*=IF(G13="eileenslounge",1000,F14E15)**			*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
14	5	6	eileenslounge2	*=IF(G14="eileenslounge",1000,F15E16)**			*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
15	6	7	Others	*=IF(G15="eileenslounge",1000,F16E17)**			*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
16	7	8
17	8

Worksheet: data

**DocAElstein** · 05-13-2020, 12:58 PM

In Support of this forum question
https://eileenslounge.com/viewtopic....268481#p268481

_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	E	F	G	H	I	J	L	N
5							CSE equivalent	CSE equivalent
6				*=IF(G6="eileenslounge",1000,F7E8)**		=IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
7	Title 5	Title 6	Title 7	Title 8	Title 9	Title 10
8			eileenslounge				*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
9		1	eileenslounge1				*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
10	1	2	eileenslounge2				*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
11	2	3	Others				*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
12	3	4	eileenslounge				*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
13	4	5	eileenslounge1				*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
14	5	6	eileenslounge2				*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
15	6	7	Others				*=IF(G8:G15="eileenslounge",1000,F9:F16E10:E17)**	=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
16	7	8
17	8
18

Worksheet: data

_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	E	F	G	H	I	J	L	N
5							CSE equivalent	CSE equivalent
6				#VALUE!		Got missing number in column F
7	Title 5	Title 6	Title 7	Title 8	Title 9	Title 10
8			eileenslounge				1000	Got one or more missing numbers
9		1	eileenslounge1				4	Got one or more missing numbers
10	1	2	eileenslounge2				9
11	2	3	Others				16
12	3	4	eileenslounge				1000
13	4	5	eileenslounge1				36
14	5	6	eileenslounge2				49
15	6	7	Others				64
16	7	8
17	8
18