Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

I'm trying to remove duplicates, but I need to remove both duplicates, not just one of the duplicates. That's what I'm getting when I filter 'unique records'

For example, I have a list of all goons:

Andy
Bob
Carl
Dave
Eddy
Frank
George

Then I have a list of goons that have girlfriends:

Andy
Carl
Eddy
Frank

Combining the lists gives me:

Andy
Andy
Bob
Carl
Carl
Dave
Eddy
Eddy
Frank
Frank
George

If I combine then and filter for unique, I end up getting the original list of all goons. Basically it filters out all the duplicates (from the girlfriend list).

But what I'd like to end up with is:

Bob
Dave
George

Goons without girlfriends. Basically combine the lists and remove BOTH references to anyone who is on the list twice. Or, extract only the people who appear once.

Is there a simple way to do this?

# ¿ Apr 20, 2011 17:58

Adbot: ADBOT LOVES YOU

# ¿ May 6, 2024 16:16

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

I ended up doing a conditional format based on duplicates, then sorting by color and just deleting the colored ones.

# ¿ Apr 20, 2011 18:39

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

I have a big list of string values and want to categorize them. My idea is make a list of keywords to search each string for, and go from there.

Colors Fruits
Red Apple
Blue Pear
Gray Melon
Pink Pepper

So if any of those 'colors' are in the string, the category becomes 'Colors', but if the substring 'Pear' is found, the category becomes 'Fruits'. What do I need to learn to do? The lookup and the substring thing, right?

I want to do it this way instead of hammering out some massive compound if/else formula.

# ¿ Sep 9, 2015 20:11

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

What's the best way to go from this:

code:

item1 qty1
item2 qty2
item3 qty3

To this?

code:

item1 qty1 item2 qty2 item3 qty3

The built-in transpose doesn't quite do what I want, can I customize that or do I have to go some other way?

Transpose does something like this:

code:

item1 item2 item3
qty1  qty2  qty3

Would it be easier to just do that first, and then perform another operation on that?

# ¿ Aug 18, 2016 17:53

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

fosborb posted:

Assuming your dataset is A1:B3, use the following formula in any cell on the worksheet and stretch it to the right:

=OFFSET($A$1,ROUND(COLUMN(A1)/2,0)-1,MOD(COLUMN(A1)-1,2))

so that cell 1 is above, and cell 2 is the following:
=OFFSET($A$1,ROUND(COLUMN(B1)/2,0)-1,MOD(COLUMN(B1)-1,2))

and so on.

There are 1000's of items and they can have up to 100 columns so I just exported to CSV, looped through it with Python and wrote a new CSV out. Bleh.

# ¿ Aug 19, 2016 18:06

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

COOL CORN posted:

I'm trying to figure out how to filter out the scenarios, for example, "I only have maps 21 and 24, I want to color all of the scenarios that use OTHER maps red" or something. Should I use VBA to split that column into an array and then use that array to selectively color the rows?

=SUMPRODUCT(--ISNUMBER(SEARCH({"21","24"},B10)))>0

It will return 1 if it matches 1, 2 if it matches 2....you could color it on that

# ¿ Aug 19, 2016 18:17

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

COOL CORN posted:

Almost perfect, but matching single number is a problem. I guess I could use leading 0s.

(i.e. SEARCH({"1"}, B10) is matching 21, 31, 41, etc.)

edit-- Is there a way I can save the array in a hidden cell and refer to it? Having {"1", "2", "3"} in cell AA2, and then using SEARCH(AA2, B10) isn't working.

What if you put them all in a range of cells and just used that range (AA1:AA5 for example) to reference them?

# ¿ Aug 19, 2016 21:32

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

Bob Morales posted:

What's the best way to go from this:
code:
item1 qty1
item2 qty2
item3 qty3
To this?
code:
item1 qty1 item2 qty2 item3 qty3
The built-in transpose doesn't quite do what I want, can I customize that or do I have to go some other way?

Transpose does something like this:
code:
item1 item2 item3
qty1  qty2  qty3
Would it be easier to just do that first, and then perform another operation on that?

I ended up doing it in Python

code:

# Creates a horizontal BOM from a vertical BOM

import csv

# file to read in and convert
bom_file_filename = 'FullBOM.csv' #'HTest.csv'
# file to hold the output
output_filename = 'HorizontalResults.csv'

# the last ID and part we read
last_id = ''
last_part = ''
# the list of subcomponent parts
sub_part_list = []

# open the output file for writing
output_file = open(output_filename, 'w')
# write the header
output_file.write("Internal ID,Name,Member Item,Member Quantity\n")

with open(bom_file_filename) as csvfile:
	reader = csv.DictReader(csvfile)
	for row in reader:
		current_id = row['Internal ID']
		current_part = row['Name']
		
		if current_id == last_id:
		# same part as the last row
		# add to list
			sub_part_list.append(row['Member Item'])
			sub_part_list.append(row['Member Quantity'])
		else:
		# this row contains a new part
		# output the old part and list
			if len(sub_part_list) > 0:
				s = "%s,%s,%s\n" % (last_id, last_part, ','.join(sub_part_list))
				output_file.write(s)
				# start new list
				sub_part_list = []
			# add to it
			sub_part_list.append(row['Member Item'])
			sub_part_list.append(row['Member Quantity'])
			last_id = current_id
			last_part = current_part

# done reading the file
# write out the last part we read in
if len(sub_part_list) > 0:
	s = "%s,%s,%s\n" % (last_id, last_part, ','.join(sub_part_list))
	output_file.write(s)

# close the output file
output_file.close()

# ¿ Aug 23, 2016 20:20

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

fosborb posted:

That's pretty slick!

So what kind of clusterfuck are you in where you legitimately have to pull out python to manhandle Excel data, and also that you're loving about with Excel data AND have permissions to execute python scripts at all?

Exporting data from one ERP system to another. Not sure why they want it in that format when they have imported it before in the regular horizontal format.

code:

Happy_Meal Hamburger 1
Happy_Meal Cup 1
Happy_Meal Lid 1
Happy_Meal Straw 1
Happy_Meal Drink 1
Happy_Meal Fries 40
Happy_Meal Fries_Bag 1
Happy_Meal Box 1

Becomes:

code:

Happy_Meal Hamburger 1 Cup 1 Lid 1 Straw 1Drink 1 Fries 40 Fries_Bag 1 Box 1

The the fun part is we have part numbers that START WITH LEADING ZEROS. 00694 that kind of poo poo

Then add in the fact that we have part numbers like 1234.10 1234.20 with TRAILING ZEROS

So you have to be ultra-careful at every step of importing, exporting to make sure you don't lose those. Excel loves to HELP YOU OUT and hide data especially with CSV's. gently caress.

# ¿ Aug 24, 2016 15:40

Adbot: ADBOT LOVES YOU

# ¿ May 6, 2024 16:16

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

Can you match a whole row using VLOOKUP or something and not just a cell?

code:

BOB  New York New York   Sales        JOE  Megacorp $50,000   Single   45
JOE  Detroit  Michigan   Accounting   JOE  ACME Co  $65,000   Married  36

So you a spreadsheet of people's information and you want to join the rows up with another spreadsheet of different info but the same people. There's like 100 columns on each sheet so I wanted to avoid doing 100 VLOOKUP formulas

Edit: Nevermind - I worked around it. I had them sorted 2 different ways, so I just did a 'sorted_order' column, 1-1000, then I sorted them the same way to make them line up, then re-sorted back on the sold sorted_order column to go back to the other way I had them sorted.

Bob Morales fucked around with this message at 22:38 on Feb 3, 2017

# ¿ Feb 3, 2017 21:23

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread